package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.common.reconsitution.DefaultValueConstant;
import com.iris.live.services.common.reconsitution.SqlConstant;
import com.iris.live.services.data.models.GroupAreaModel;
import com.iris.live.services.data.models.Lsh车型Model;
import com.iris.live.services.data.models.SnpSettingModel;
import com.iris.live.services.data.models.报价精品详细表Model;
import com.iris.live.services.data.repositories.GroupAreaRepository;
import com.iris.live.services.data.repositories.Lsh车型Repository;
import com.iris.live.services.data.repositories.SNPSettingRepository;
import com.iris.live.services.data.repositories.SetRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.SetService;
import org.apache.commons.logging.LogFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;

import java.math.BigDecimal;
import java.util.*;

@Repository
@Transactional
public class SetRepositoryImpl extends BaseRepository implements SetRepository {
    private static Logger logger = LoggerFactory.getLogger(SetRepositoryImpl.class);

    @Autowired
    private SNPSettingRepository snpSettingRepository;

    @Autowired
    private Lsh车型Repository lshCarTypeRepository;

    @Autowired
    private GroupAreaRepository groupAreaRepository;

    @Override
    public List<String> getItaskSetItems(String permit, String setItem) {
        Map<String, String> params = Maps.newHashMap();
        //bug 5580 中描述，由姜老师提出 邀约名单生成日期 默认是 1 并且不能修改
        String sql = "SELECT `设置项目`, IF(`值` = '邀约名单生成日期', '1',  `值`) 值 " + "FROM `itask基础设置` " + "WHERE 1=1";

        if (!Strings.isNullOrEmpty(setItem)) {
            String[] setItemArray = setItem.split(",");
            int length = setItemArray.length;
            for (int i = 0; i < length; i++) {
                if (i == 0) {
                    sql += " and (设置项目 = '" + setItemArray[i] + "' ";
                } else {
                    sql += " or 设置项目 = '" + setItemArray[i] + "' ";
                }
            }
            sql += ")";
        }

        if (!Strings.isNullOrEmpty(permit)) {
            sql += " and 许可 = :permit";
            params.put("permit", permit);
        }
        return this.getList(sql, params);
    }

    @Override
    public List<String> getRootByRole(String permit, List alliist) {
        Map<String, String> params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT C.`角色` 岗位, ");
        if (alliist != null && alliist.size() > 0) {
            for (int i = 0; i < alliist.size(); i++) {
                String mingzi = alliist.get(i).toString();
                if (i == alliist.size() - 1) {
                    sql.append("  MAX(    " +
                            " CASE B.itemName   " +
                            "   WHEN  '" + mingzi + "'    " +
                            "THEN  A.roleId  ELSE    " +
                            "    0 END ) '" + mingzi + "'  ");
                } else {
                    sql.append("  MAX(    " +
                            " CASE B.itemName   " +
                            "   WHEN  '" + mingzi + "'   " +
                            "THEN  A.roleId  ELSE    " +
                            "    0 END ) '" + mingzi + "'  ,");
                }

            }
        }
        sql.append("FROM `LSH角色表` AS C    " +
                "LEFT JOIN ( SELECT roleId, itemId FROM roleItem    " +
                "  WHERE permit =:permit) AS A ON A.roleId = C.`角色编号`     " +
                "LEFT JOIN itemInfo AS B ON A.itemId = B.id GROUP BY C.`角色`");
        return this.getList(sql.toString(), params);
    }
    public PageData getDepositList(SetService.inOutAccountRequest re, String permit) {
        Map<String, String> params = Maps.newHashMap();
        params.put("permit",permit);
        PageData pg;
        StringBuilder sql = new StringBuilder("SELECT    " +
                "  a.sum1 as 金额,      " +
                "  if(FORMAT(a.sum1,4) = FORMAT(a.sum2,4) ,'已确认','待确认' )  as 状态,    " +
                "  a.*,      " +
                "  count(DISTINCT a.报价编号)      " +
                "from (    " +
                "  SELECT DISTINCT       " +
                "   IF(CASE\n" +
                "      WHEN NOT ISNULL(bjjc.`交车日期`) THEN '18'\n" +
                "      WHEN (NOT ISNULL(bjjc.`开票时间`) AND ISNULL(bjjc.`退票时间`))\n" +
                "\tOR (NOT ISNULL(bjjc.`开票时间`) AND NOT ISNULL(bjjc.`退票时间`) AND bjjc.`开票时间`>bjjc.`退票时间`)\n" +
                "\t  THEN '17'\n" +
                "      WHEN NOT ISNULL(bjjc.`退票时间`) AND l.`订金类型`='订金' THEN '13'\n" +
                "      WHEN NOT ISNULL(l.`订单失销日期`) AND (l.`订单状态`='退订C') THEN '8'\n" +
                "      WHEN NOT ISNULL(e.`应付金额`) AND NOT ISNULL(e.`实付金额`) AND ROUND(e.`实付金额`,4)>=ROUND(e.`应付金额`,4) THEN '15'\n" +
                "      WHEN NOT ISNULL(bjjc.`合同签约日期`) THEN '16'\n" +
                "      WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='订金' THEN '13'\n" +
                "      WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='意向金' THEN '12'\n" +
                "      ELSE '10'\n" +
                "      END ='12','意向金','订金') 付款目的,    \n" +
                "\n" +
                "      IFNULL((SELECT g.应付金额 FROM `报价付款详细` g WHERE  g.付款事由 = \n" +
                "\t\tIF(CASE  WHEN NOT ISNULL(bjjc.`交车日期`) THEN '18'\n" +
                "                    WHEN (NOT ISNULL(bjjc.`开票时间`) AND ISNULL(bjjc.`退票时间`))\n" +
                "                OR (NOT ISNULL(bjjc.`开票时间`) AND NOT ISNULL(bjjc.`退票时间`) AND bjjc.`开票时间`>bjjc.`退票时间`)  THEN '17' \n" +
                "                      WHEN NOT ISNULL(bjjc.`退票时间`) AND l.`订金类型`='订金' THEN '13'  \n" +
                "                      WHEN NOT ISNULL(l.`订单失销日期`) AND (l.`订单状态`='退订C') THEN '8'  \n" +
                "                      WHEN NOT ISNULL(e.`应付金额`) AND NOT ISNULL(e.`实付金额`) AND ROUND(e.`实付金额`,4)>=ROUND(e.`应付金额`,4) THEN '15' \n" +
                "                      WHEN NOT ISNULL(bjjc.`合同签约日期`) THEN '16' \n" +
                "                      WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='订金' THEN '13' \n" +
                "                    WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='意向金' THEN '12' \n" +
                "                    ELSE '10'  END ='12','意向金','订金')                     \n" +
                "                    AND g.报价编号= d.`报价编号`),0.00) AS sum1, \n" +
                " \tIFNULL( (SELECT g.实付金额 FROM `报价付款详细` g WHERE g.付款事由 = IF(CASE  WHEN NOT ISNULL(bjjc.`交车日期`) THEN '18'\n" +
                "                    WHEN (NOT ISNULL(bjjc.`开票时间`) AND ISNULL(bjjc.`退票时间`))\n" +
                "                OR (NOT ISNULL(bjjc.`开票时间`) AND NOT ISNULL(bjjc.`退票时间`) AND bjjc.`开票时间`>bjjc.`退票时间`)  THEN '17' \n" +
                "                      WHEN NOT ISNULL(bjjc.`退票时间`) AND l.`订金类型`='订金' THEN '13'  \n" +
                "                      WHEN NOT ISNULL(l.`订单失销日期`) AND (l.`订单状态`='退订C') THEN '8'  \n" +
                "                      WHEN NOT ISNULL(e.`应付金额`) AND NOT ISNULL(e.`实付金额`) AND ROUND(e.`实付金额`,4)>=ROUND(e.`应付金额`,4) THEN '15' \n" +
                "                      WHEN NOT ISNULL(bjjc.`合同签约日期`) THEN '16' \n" +
                "                      WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='订金' THEN '13' \n" +
                "                    WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='意向金' THEN '12' \n" +
                "                    ELSE '10'  END ='12','意向金','订金')  AND g.报价编号= d.`报价编号`),NULL) sum2,   "    +
                "                 b.`车型` 意向车型,    \n" +
                "    b.`车型详细` 意向车款,        " +
                "    if(locate('IRIS',a.`车架号`) > 0,'',a.`车架号`) as`车架号(VIN)`,         " +
                "    b.`品牌`,    " +
                "    b.`级别`,    " +
                "    b.`车型`,    " +
                "    b.`车型详细`,       " +
                "    l.`订单客户姓名` 客户名称,      " +
                "    N .`电话号码` 客户电话,      " +
                "    k.`人员姓名` 销售顾问,      " +
                "        CASE  " +
                "      WHEN NOT ISNULL(bjjc.`交车日期`) THEN '18'  " +
                "      WHEN (NOT ISNULL(bjjc.`开票时间`) AND ISNULL(bjjc.`退票时间`))  " +
                "   OR (NOT ISNULL(bjjc.`开票时间`) AND NOT ISNULL(bjjc.`退票时间`) AND bjjc.`开票时间`>bjjc.`退票时间`)  " +
                "     THEN '17'  " +
                "      WHEN NOT ISNULL(bjjc.`退票时间`) AND l.`订金类型`='订金' THEN '13'  " +
                "      WHEN NOT ISNULL(l.`订单失销日期`) AND (l.`订单状态`='退订C') THEN '8'  " +
                "      WHEN NOT ISNULL(e.`应付金额`) AND NOT ISNULL(e.`实付金额`) AND ROUND(e.`实付金额`,4)>=ROUND(e.`应付金额`,4) THEN '15'  " +
                "      WHEN NOT ISNULL(bjjc.`合同签约日期`) THEN '16'  " +
                "      WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='订金' THEN '13'  " +
                "      WHEN NOT ISNULL(l.`订单审批通过日期`) AND ISNULL(l.`订单失销日期`) AND l.`订金类型`='意向金' THEN '12'  " +
                "      ELSE '10'  " +
                "      END AS 新车销售状态,     " +
                "    l.`销售类型` `销售类型`,       " +
                "    e.`实付金额` `实际到账总额`,       " +
                "    l.`订单审批通过日期` 排序时间 ,      " +
                "    d.`报价编号` 报价编号,      " +
                "    d.初始报价单号      " +
                "  from 报价单表 AS d       " +
                "  JOIN (        " +
                "    SELECT DISTINCT quotedPriceCodeOrVIN        " +
                "    FROM pushapprovalworkflow c         " +
                "    INNER JOIN (SELECT id, concat(step, transcation_code) asss FROM pushapprovalworkflow WHERE PERMIT=:permit) a on c.id=a.id       " +
                "    INNER JOIN (SELECT concat(max(step), transcation_code) ass FROM pushapprovalworkflow WHERE PERMIT=:permit GROUP BY transcation_code) b on b.ass=a.asss        " +
                "    where c.type IN (1, 2, 3 ,6, 7,10)    " +
                "    AND PERMIT = :permit     " +
                "    AND (c.approvalstatus = 1 OR c.approvalstatus = 3 OR c.approvalstatus is NULL)      " +
                "    AND locate('/', c.transcation_code) = 0       " +
                "  ) AS k ON k.quotedPriceCodeOrVIN = d.报价编号        " +
                "  INNER JOIN `报价付款详细` AS e ON d.`报价编号` = e.`报价编号`       " +
                "  LEFT JOIN `LSH库存表` AS a ON a.`报价编号` = d.`报价编号`       " +
                "  INNER JOIN `LSH车型` AS b ON b.`车型编号` = d.`车型编号`       " +
                "  INNER JOIN `用户` AS k ON d.`销售顾问` = k.`用户名`       " +
                "  INNER JOIN `报价订单表` AS l ON d.`订单编号` = l.`订单编号`        " +
                "  INNER JOIN `顾客记录汇总` AS n ON d.`顾客编号` = n.`顾客编号`       " +
                "  INNER JOIN `vw_max_quoted_code` AS o  ON d.`报价编号`=o.`报价编号` "+
                "  left join  报价交车表 bjjc on bjjc.编号=d.交车  "+
                "  where 1=1      ");

        if(!Strings.isNullOrEmpty(re.IntentionalVehicle)){
            // sql.append(" and a.意向车型 like '%"+re.IntentionalVehicle+"%' ");
            sql.append( " and  (  b.`品牌` LIKE  '%"+re.IntentionalVehicle+"%' " +
                    "OR b.`级别` LIKE  '%"+re.IntentionalVehicle+"%' " +
                    "OR b.`车型` LIKE  '%"+re.IntentionalVehicle+"%' " +
                    "OR b.`车型详细` LIKE  '%"+re.IntentionalVehicle+"%'  )");
        }
        sql.append("    ORDER  by  d.`报价编号` desc  ) a   where 1=1  ");
        if(!Strings.isNullOrEmpty(re.paymentPurpose)){
            sql.append(" and a.付款目的='"+re.paymentPurpose+"' ");
        }
        if(!Strings.isNullOrEmpty(re.saleType)){
            sql.append(" and  a.销售类型 ='"+re.saleType+"' ");
        }
        if(!Strings.isNullOrEmpty(re.newCarSaleStatus)){

            sql.append(" and  a.新车销售状态 ='"+re.newCarSaleStatus+"' ");
        }

        if(!Strings.isNullOrEmpty(re.vin)){

            sql.append(" and  a.`车架号(VIN)` like :vin ");
            params.put("vin","%"+re.vin+"%");
        }
        if(!Strings.isNullOrEmpty(re.cusName)){
            sql.append(" and  a.客户名称 like '%"+re.cusName+"%' ");
        }
        pg=this.getPage(sql.append("  GROUP BY  a.初始报价单号   order by 状态 desc, 排序时间 desc  ").toString(),params,re.getPageSize(),re.getPageIndex());
        return pg;
    }
    @Override
    public List<String> getAllLshRole() {
        String sql = "SELECT 角色编号,角色 FROM `LSH角色表`";
        return this.getList(sql);
    }

    @Override
    public Map getAllLshRoleName() {
        String sql = "SELECT 角色 FROM `LSH角色表`";
        List a= this.getList(sql);
        Map result=new HashMap();
        for(Object n:a){
            String name = (String) ((Map) n).get("角色");
            result.put(name, null);
        }
        return  result;
    }

    @Override// 按 陈晓琪逻辑修改的 sql
    public PageData getAllCarPolicy(SetService.GetCarPolicyRequest carPolicyReq) {
        Map<String, String> params = Maps.newHashMap();

        String sql = "SELECT `BMBS_release_date` `BMBS发布日期`,	`si_program_code_english` `随车SI编码`,a.车型编号,SI_cost as SI成本,	"
                + "si_program_name_chinese `随车SI名称(中文)`,start_date `生效日期`,end_date `终止日期`,"
                + "item_number 条目数,if(a.`车型详细`<>'',a.`车型详细`,if(a.`车型`<>'',a.`车型`,if(a.`级别`<>'',a.`级别`,if(a.`品牌`<>'',a.`品牌`,'')))) `适用车型`,"
                + "b.`颜色说明` `颜色`,item_type `SI类型`,item `随车SI项目`,NC_Type `NC分类`,"
                + "finance_product_number `金融产品编码`,SI_money `随车SI金额`,SI_condition `随车SI条件`,SI_content `随车SI内容`,"
                + "create_date 创建日期,modify_date 修改日期,"
                + "IF (SI_status = '1','启用',IF (SI_status = '2','停用','删除')) SI状态,"
                + "IF (release_status is NULL,'未发布',release_status) `发布状态`,remark 备注, '删除' 操作, `area` `适用区域`,`id`"
                + "FROM attachmentPolicy "
                + "LEFT JOIN (select * from (  SELECT   " +
                "   e.*  " +
                "      FROM lsh车型 AS d   " +
                "      JOIN (SELECT c.车型编号,c.品牌,c.来源,c.级别,c.车型,c.车型详细 FROM lsh车型 AS c) AS e   " +
                "         ON (d.品牌 = e.品牌  " +
                "         AND d.来源 = e.来源  " +
                "         AND d.级别 = e.级别  " +
                "         AND d.车型 = e.车型  " +
                "         AND d.车型详细 = e.车型详细)  " +
                "       ORDER BY 车型编号 ) a  GROUP BY  a.品牌,a.来源,a.级别,a.车型,a.车型详细 ) AS a ON a.`车型编号` = car_code "
                + "LEFT JOIN `LSH车型外观` AS b ON apperance_code = b.`车型外观编号` "
                + "WHERE 1=1 ";

        String permit = carPolicyReq.permit;
        if (!Strings.isNullOrEmpty(permit)) {
            sql += "and permit = :permit   ";
            params.put("permit", permit);
        }

        String si_program_code_english = carPolicyReq.si_program_code_english;
        if (!Strings.isNullOrEmpty(si_program_code_english)) {
            sql += "and si_program_code_english like :si_program_code_english   ";
            params.put("si_program_code_english", "%" + si_program_code_english
                    + "%");
        }

        String item = carPolicyReq.item;
        if (!Strings.isNullOrEmpty(item)) {
            sql += "and item like :item   ";
            params.put("item", "%" + item + "%");
        }

        String si_program_name_chinese = carPolicyReq.si_program_name_chinese;
        if (!Strings.isNullOrEmpty(si_program_name_chinese)) {
            sql += "and si_program_name_chinese like :si_program_name_chinese   ";
            params.put("si_program_name_chinese", "%" + si_program_name_chinese
                    + "%");
        }

        String carType = carPolicyReq.carType;
        if (!Strings.isNullOrEmpty(carType)) {
            sql += "and a.`车型详细` like :carType   ";
            params.put("carType", "%" + carType + "%");
        }

        String si_money_min = carPolicyReq.si_money_min;
        if (!Strings.isNullOrEmpty(si_money_min)) {
            sql += "and SI_money >= :si_money_min ";
            params.put("si_money_min", si_money_min);
        }

        String si_money_max = carPolicyReq.si_money_max;
        if (!Strings.isNullOrEmpty(si_money_max)) {
            sql += "and SI_money <= :si_money_max ";
            params.put("si_money_max", si_money_max);
        }

        sql += "ORDER BY 随车SI编码 ";
        return this.getPage(sql, params, carPolicyReq.getPageSize(),
                carPolicyReq.getPageIndex());
    }

    @Override
    public List<String> getAllCarDetailInfo() {
        String sql = "SELECT a.`颜色说明`, if(b.`车型详细`='','', b.`车型详细`) `车型`,"
                + " a.`车型外观编号` " + "FROM `LSH车型外观` AS a "
                + "LEFT JOIN `LSH车型` AS b ON b.Variant = a.Variant";
        return this.getList(sql);
    }

    @Override
    public List<String> getAllCarTypeInfo() {
        String sql = "SELECT `车型编号`,if(`车型详细`<>'',`车型详细`,if(`车型`<>'',`车型`,if(`级别`<>'', `级别`,if(`品牌`<>'',`品牌`,'')))) 车型详细,"
                + " '0' 状态 " + "FROM `LSH车型`";
        return this.getList(sql);
    }

    @Override
    public List<String> getCarColorByCarType(String carParam) {
        String sql = "SELECT a.`颜色说明`, b.`车型编号` " + "FROM `LSH车型外观` AS a "
                + "LEFT JOIN `LSH车型` AS b ON b.Variant = a.Variant "
                + "WHERE 1=1";
        if (!Strings.isNullOrEmpty(carParam)) {
            String[] params = carParam.split(",");
            int length = params.length;
            for (int i = 0; i < length; i++) {
                if (i == 0) {
                    sql += " and b.车型编号 = '" + params[i] + "'";
                } else {
                    sql += " and a.`颜色说明` in "
                            + "(SELECT a.`颜色说明` "
                            + "FROM `LSH车型外观` AS a "
                            + "LEFT JOIN `LSH车型` AS b ON b.Variant = a.Variant "
                            + "WHERE b.车型编号 = '" + params[i] + "')   ";
                }
            }
        }
        return this.getList(sql);
    }

    @Override
    public List<String> getDisplayFlowInfo(String permit, Date startDate,
                                           Date endDate) {
        Map<String, Object> params = Maps.newHashMap();

        String sql = " SELECT a.进店编号,d.`人员姓名` AS 操作人,if(a.`是否首次到店`='是','1','2') AS 是否首次到店,  " +
                     "        if (date_format(a.`到店时间`,'%k')=3 , date_format(a.`到店时间`,'1:%i:%s') , date_format(a.`到店时间`,'%k:%i:%s'))`到店时间`,  " +
                     "        if (date_format(a.`离店时间`,'%k')=3 , date_format(a.`离店时间`,'1:%i:%s') , date_format(a.`离店时间`,'%k:%i:%s')) `离店时间`,a.`停留时间`,  " +
                     "        if(a.`是否预约` ='是','1','0') AS 是否预约,if(a.`是否指定销售顾问` IS NOT NULL,'1','0') 是否指定销售顾问,a.`是否指定销售顾问` 指定销售顾问,a.`销售顾问`,  " +
                     "        if(c.`顾客姓名`='' or c.`顾客姓名` is null,'访客',c.`顾客姓名`) as 顾客姓名,if(c.`电话号码`='' or c.`电话号码` is null,'NA',c.`电话号码`) as 电话号码,a.`意向车型A`,a.`保有品牌`,a.`客户级别`,a.`顾客来源` 首次来源渠道,  " +
                     "        a.`信息渠道` 渠道细分备注,a.`顾客再次来源`,if(a.`是否报价`='是','1','0') as 是否报价,if(a.`是否订单`='是','1',if(a.`是否订单`='退订','-1','0')) as 是否订单,  " +
                     "        if(a.`是否交车`='是','1','0') as 是否交车,a.`到店目的`,a.`接待备注`,  " +
                     "        a.`进店日期`,a.`意向车型B`,a.`意向车型C` " +
                     " FROM `顾客进店记录表` AS a   " +
                     "       LEFT JOIN `用户` AS d ON a.`操作人` = d.`用户名`   " +
                     "       LEFT JOIN `顾客记录汇总` AS c ON c.`顾客编号` = a.`顾客编号` " +
                     " WHERE a.许可 = :permit ";
        params.put("permit", permit);

        if (startDate != null && endDate != null) {
            sql += " and a.`进店日期` >= date_format(:startDate,'%Y-%m-%d') AND a.`进店日期` <= date_format(:endDate,'%Y-%m-%d')";
            params.put("startDate", startDate);
            params.put("endDate", endDate);
        }

        return this.getList(sql, params);
    }

    @Override
    public List<String> filterCarType(List<String> brands) {
        String sql = "SELECT DISTINCT t1.`品牌`, " + " IF (t1.`来源` = 'PbP',CONCAT('PbP ', t1.`级别`),t1.`级别`) `车系`,t1.`车型`,"
                + " `车型详细` `车款`, MSRP `BI车款标配MSRP（含税）`, '' `现金折扣+经销商随车成本上限`,"
                + " '' `随车SI成本`, '' `标配区域限价（SNP）(含税)`, '' `厂家零售SI`," + " '' `厂家批售SI`, '' `BMBS TA`, '' `LSH TA` "
                + " FROM `LSH车型` t1 " + " WHERE t1.车型详细 <> '' ";

        if(brands != null && !brands.isEmpty()){
            sql += " AND 品牌 in ('1'";
            for(String brand : brands){
                sql += ",'" + brand + "'";
            }
            sql += ")";
        }
        sql += " GROUP BY t1.`品牌`, t1.`级别`,t1.`车型`,t1.`车型详细` ORDER BY t1.`品牌`, t1.`级别`,t1.`车型`,t1.`车型详细`";

        return getList(sql);
    }

    // 获取车型颜色和外观
    @Override
    public List<String> getCarColorAndAppearance() {
        String sql = "SELECT `颜色说明`, `外观代码` FROM `LSH车型外观`;";
        return this.getList(sql);
    }

    // 获取最大条目数
    @Override
    public List<String> getMaxEntryNumber() {
        String sql = "SELECT max(item_count)+1 item_count from dealerAttachmentPolicy";
        return this.getList(sql);
    }

    //@Override
    @Transactional
    public boolean saveBatchSnpSettingData_old(
            List<SetService.SNPSettingRequest> snpDataList) {
        Map<String, Object> params = Maps.newHashMap();

        String selCarCodeSql = "select 车型编号 " + "from LSH车型 "
                + "where 车型详细=:carDetail";

        String selCarColorSql = "select 车型外观编号 " + "from LSH车型外观 "
                + "where 颜色说明=:carColor";

        int i = 0;
        for (SetService.SNPSettingRequest setReq : snpDataList) {
            int cardCode = 0;
            int apperanceCode = 0;

            try {
                String carDetail = Strings.isNullOrEmpty(setReq.carDetail) ? "0"
                        : setReq.carDetail;
                params.put("carDetail", carDetail);

                List cardCodeRes = this.getList(selCarCodeSql, params);
                if (cardCodeRes != null && cardCodeRes.size() > 0) {
                    Map resMap = (Map) cardCodeRes.get(0);
                    cardCode = (int) resMap.get("车型编号");
                }
                params.clear();

                String carColor = Strings.isNullOrEmpty(setReq.carColor) ? ""
                        : setReq.carColor;
                params.put("carColor", carColor);

                List carColorCodeRes = this.getList(selCarColorSql, params);
                if (carColorCodeRes != null && carColorCodeRes.size() > 0) {
                    Map resMap = (Map) carColorCodeRes.get(0);
                    apperanceCode = (int) resMap.get("车型外观编号");
                }
                params.clear();

                String sql = "select id " + "from SNP_setting "
                        + "where carCode = :cardCode "
                        + "and apperanceCode = :apperanceCode "
                        + "and y_m = '190001'";

                params.put("cardCode", cardCode);
                params.put("apperanceCode", apperanceCode);

                List resList = this.getList(sql, params);
                params.clear();

                SnpSettingModel snpSettingModel = new SnpSettingModel();
                if (resList != null && resList.size() > 0) {
                    Map resMap = (Map) resList.get(0);
                    int id = (int) resMap.get("id");

                    snpSettingRepository.updateSnpSettingData(setReq
                                    .getBiStandardConfMSRPTaxIncluded(), setReq
                                    .getCashDiscountAndSICostCelling(), setReq
                                    .getSiCost(), setReq
                                    .getStandardConfigAreaPriceLimitSNPTaxIncluded(),
                            setReq.getBmbsWholeSaleSI(), setReq
                                    .getBmbsWholeSaleSI(), id);
                } else {
                    snpSettingModel.setPermit(setReq.permit);
                    snpSettingModel.setCarCode(cardCode);
                    snpSettingModel.setApperanceCode(apperanceCode);
                    snpSettingModel.setBiStandardConfigMsrpTaxIncluded(setReq
                            .getBiStandardConfMSRPTaxIncluded());
                    snpSettingModel.setCashDiscountAndSiCostCelling(setReq
                            .getCashDiscountAndSICostCelling());
                    snpSettingModel.setSiCost(setReq.getSiCost());
                    snpSettingModel
                            .setStandardConfigurationAreaPriceLimitSnpTaxIncluded(setReq
                                    .getStandardConfigAreaPriceLimitSNPTaxIncluded());
                    snpSettingModel.setBmbsRetailSi(setReq.getBmbsRetailSI());
                    snpSettingModel.setBmbsWholesaleSi(setReq
                            .getBmbsWholeSaleSI());

                    snpSettingModel.setyM("190001");

                    snpSettingRepository.save(snpSettingModel);
                }

                if (i == 0) {
                    snpSettingRepository.deleteSnpDataByYmAndPermit(setReq.ym,
                            setReq.permit);
                    i++;
                }

                SnpSettingModel snpSettingModel2 = new SnpSettingModel();
                snpSettingModel2.setPermit(setReq.permit);
                snpSettingModel2.setCarCode(cardCode);
                snpSettingModel2.setApperanceCode(apperanceCode);
                snpSettingModel2.setBmbsTa(setReq.getBmbsTA());
                snpSettingModel2.setLshTa(setReq.getLshTA());
                snpSettingModel2.setyM(setReq.ym);
                snpSettingRepository.save(snpSettingModel2);

            } catch (Exception e) {
                logger.error("运行异常。", e);
                return false;
            }
        }
        return true;
    }

    @Transactional
    public String saveBatchSnpSettingData(List<SetService.SNPSettingRequest> snpDataList) {
        String result = "";
        Map<String, Object> params = Maps.newHashMap();
        String selCarCodeSql = "select 车型编号  from LSH车型  where 车型详细=:carDetail order by 车型编号";
        String sql_TA = "select id from SNP_setting where carCode = :cardCode"
                + " and y_m = :ym and permit = :permit";
        String sql_SI = "select id from SNP_setting where carCode = :cardCode"
                + " and apperanceCode = :apperanceCode and y_m = '190001' and permit= :permit";
        String sql_car_color = "SELECT t1.车型编号,t2.车型外观编号 FROM LSH车型 t1, LSH车型外观 t2"
                + " WHERE t1.variant=t2.variant AND t2.颜色说明=:carColor AND t1.车型详细=:carDetail order by t1.车型编号,t2.车型外观编号 ";


        for (SetService.SNPSettingRequest setReq : snpDataList) {
            int cardCode = 0;
            int apperanceCode = 0;

            // 如果车型编号为空，则不处理
            if (Strings.isNullOrEmpty(setReq.carDetail)) {
                result += "车款为空;";
                continue;
            }

            try {
                //step 1查询车型编号,车型外观编号
                if (Strings.isNullOrEmpty(setReq.carColor)) {
                    //无颜色
                    //查询车型编号
                    params.clear();
                    params.put("carDetail", setReq.carDetail);
                    List cardCodeRes = this.getList(selCarCodeSql, params);
                    if (cardCodeRes != null && !cardCodeRes.isEmpty()) {
                        Map resMap = (Map) cardCodeRes.get(0);
                        cardCode = (int) resMap.get("车型编号");
                    } else {
                        // 如果车型编号不匹配，则不处理
                        result += setReq.carDetail + "车款不匹配;";
                        continue;
                    }
                } else {
                    //有颜色的情况：
                    //查询车型编号,车型外观编号
                    params.clear();
                    params.put("carDetail", setReq.carDetail);
                    params.put("carColor", setReq.carColor);
                    List cardCodeRes = this.getList(sql_car_color, params);
                    if (cardCodeRes != null && !cardCodeRes.isEmpty()) {
                        Map resMap = (Map) cardCodeRes.get(0);
                        cardCode = (int) resMap.get("车型编号");
                        apperanceCode = (int) resMap.get("车型外观编号");
                    } else {
                        // 如果车型和颜色不匹配，则不处理
                        result += "车款" + setReq.carDetail + "和颜色" + setReq.carColor + "不正确;";
                        continue;
                    }
                }

                //step 2更新ta--bmbsTA或lshTA大于0才更新,如果bmbsTA和lshTA都要改为0，就只能从界面上修改了
                if ((setReq.getBmbsTA() != null && setReq.getBmbsTA().compareTo(BigDecimal.ZERO) > 0)
                        || (setReq.getLshTA() != null && setReq.getLshTA().compareTo(BigDecimal.ZERO) > 0)) {
                    params.clear();
                    params.put("cardCode", cardCode);
                    params.put("ym", setReq.ym);
                    params.put("permit", setReq.permit);
                    List resList = this.getList(sql_TA, params);
                    if (resList != null && !resList.isEmpty()) {
                        Map resMap = (Map) resList.get(0);
                        snpSettingRepository.updateSnpSettingTA(setReq.getBmbsTA(), setReq.getLshTA(), (int) resMap.get("id"), apperanceCode);
                    } else {
                        SnpSettingModel snpSettingModel2 = new SnpSettingModel();
                        snpSettingModel2.setPermit(setReq.permit);
                        snpSettingModel2.setCarCode(cardCode);
                        snpSettingModel2.setApperanceCode(apperanceCode);
                        snpSettingModel2.setBmbsTa(setReq.getBmbsTA());
                        snpSettingModel2.setLshTa(setReq.getLshTA());
                        snpSettingModel2.setyM(setReq.ym);
                        snpSettingRepository.save(snpSettingModel2);
                    }
                }

                //step 3更新si
                params.clear();
                params.put("cardCode", cardCode);
                params.put("apperanceCode", apperanceCode);
                params.put("permit", setReq.permit);
                List resList = this.getList(sql_SI, params);
                if (resList != null && !resList.isEmpty()) {
                    Map resMap = (Map) resList.get(0);
                    int id = (int) resMap.get("id");
                    snpSettingRepository.updateSnpSettingData(setReq.getBiStandardConfMSRPTaxIncluded(),
                            setReq.getCashDiscountAndSICostCelling(), setReq.getSiCost(),
                            setReq.getStandardConfigAreaPriceLimitSNPTaxIncluded(), setReq.getBmbsRetailSI(),
                            setReq.getBmbsWholeSaleSI(), id);
                } else {
                    SnpSettingModel snpSettingModel = new SnpSettingModel();
                    snpSettingModel.setPermit(setReq.permit);
                    snpSettingModel.setCarCode(cardCode);
                    snpSettingModel.setApperanceCode(apperanceCode);
                    snpSettingModel.setBiStandardConfigMsrpTaxIncluded(setReq.getBiStandardConfMSRPTaxIncluded());
                    snpSettingModel.setCashDiscountAndSiCostCelling(setReq.getCashDiscountAndSICostCelling());
                    snpSettingModel.setSiCost(setReq.getSiCost());
                    snpSettingModel.setStandardConfigurationAreaPriceLimitSnpTaxIncluded(
                            setReq.getStandardConfigAreaPriceLimitSNPTaxIncluded());
                    snpSettingModel.setBmbsRetailSi(setReq.getBmbsRetailSI());
                    snpSettingModel.setBmbsWholesaleSi(setReq.getBmbsWholeSaleSI());
                    snpSettingModel.setyM("190001");

                    snpSettingRepository.save(snpSettingModel);
                }
            } catch (Exception e) {
                logger.error("数据保存异常。", e);
                // 有异常不抛出，有一行数据有问题，继续跑其他行的数据
                LogFactory.getLog(SetRepositoryImpl.class).info(e.getMessage());
                result += setReq.carDetail;
                if (!Strings.isNullOrEmpty(setReq.carColor)) {
                    result += " " + setReq.carColor;
                }
                result += ",导入失败;";
            }
        }
        if (Strings.isNullOrEmpty(result)) {
            result = "SUCCESS";
        }

        return result;
    }

    @Override
    public PageData getSNPDataByYM(SetService.GetSNPSettingReq snpSettingReq) {
        Map<String, String> params = Maps.newHashMap();

        String sql = "SELECT DISTINCT c.`品牌`, IF ( c.`来源` = 'PbP', CONCAT('PbP ', c.`级别`), c.`级别` ) `车系`,"
                + "c.`车型`, c.`车型详细` `车款`, d.`颜色说明` `颜色`, "
                + "b.BI_standard_config_MSRP_tax_included `BI车款标配MSRP（含税）`, "
                + "b.cash_discount_and_SI_cost_celling `现金折扣+经销商随车成本上限`, "
                + "b.SI_cost `随车SI成本`, b.standard_configuration_area_price_limit_SNP_tax_included `标配区域限价（SNP）(含税)`, "
                + "b.BMBS_retail_SI `厂家零售SI`, b.BMBS_wholesale_SI `厂家批售SI`, a.BMBS_TA `BMBS TA`, "
                + "a.LSH_TA `LSH TA` "
                + "FROM SNP_setting b "
                + "LEFT JOIN SNP_setting a "
                + "ON ( a.carCode = b.carCode AND a.apperanceCode = b.apperanceCode AND a.permit = b.permit AND a.y_m = :ym) "
                + "LEFT JOIN LSH车型 c ON c.`车型编号` = b.carCode "
                + "LEFT JOIN LSH车型外观 d on b.apperanceCode = d.车型外观编号 "
                + "WHERE b.y_m = '190001'";

        params.put("ym", snpSettingReq.ym);

        if (!Strings.isNullOrEmpty(snpSettingReq.permit)) {
            sql += " AND b.permit = :permit";
            params.put("permit", snpSettingReq.permit);
        }
        return getPage(sql, params, snpSettingReq.getPageSize(), snpSettingReq.getPageIndex());
    }

    @Override
    public PageData getUnSaleUserInfo(SetService.unSaleUserRequest unSaleUserReq) {
        Map<String, String> params = Maps.newHashMap();

        String sql = "SELECT u.`用户名`, " +
                "   u.`人员姓名`,  " +
                "   u.`人员电话`,  " +
                "   u.`角色`,  " +
                "   u.`品牌`,  " +
                "   u.`更新日期`,  " +
                "   u.`kpi使用状态`, " +
                "   u.`密码`,  " +
                "   u.`接待状态`,  " +
                "   u.`电子邮箱`,  " +
                "   u.`分组编号`,  " +
                "   u.所属部门,  " +
                "   u.删除状态,  " +
                "   g.area_name as 所属区域,  " +
                "   u.许可 as 所属许可,  " +
                "   u.superior  " +
                "FROM `用户` as u " +
                "LEFT JOIN group_area as g on  u.许可 = g.permit " +
                " WHERE ( `角色` IS NULL OR `角色` != '销售顾问' ) ";

        if (!Strings.isNullOrEmpty(unSaleUserReq.permit)) {
            sql += " and `许可` = :permit ";
            params.put("permit", unSaleUserReq.permit);
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.username)) {
            sql += " and `用户名` like :username ";
            params.put("username", "%" + unSaleUserReq.username + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.realname)) {
            sql += " and `人员姓名` like :realname ";
            params.put("realname", "%" + unSaleUserReq.realname + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.mobile)) {
            sql += " and `人员电话` like :mobile ";
            params.put("mobile", "%" + unSaleUserReq.mobile + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.role)) {
            sql += " and `角色` like :role ";
            params.put("role", "%" + unSaleUserReq.role + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.areaPermit)) {
            GroupAreaModel model =  groupAreaRepository.getInfoByPermit(unSaleUserReq.areaPermit);
            if(model.getAreaName().contains("集团")){
                sql += " and g.`parent_id` in(0,1) ";
            }else if(!model.getAreaName().contains("集团") && model.getIsDealer()==0){
                sql += " and g.`parent_id` in(:parentId) ";
                params.put("parentId",  String.valueOf(model.getId()) );
            }

        }

        if (null == unSaleUserReq.groupType || 0== unSaleUserReq.groupType ) {
            sql += " and u.`删除状态` != :status ";
            params.put("status", "已删除");
        }
        return this.getPage(sql, params, unSaleUserReq.getPageSize(),
                unSaleUserReq.getPageIndex());
    }

    @Override
    public int getUnSaleUserNum(SetService.unSaleUserRequest unSaleUserReq) {
        Map<String, String> params = Maps.newHashMap();

        String sql = "SELECT COUNT(`用户名`) `行数`"
                + "FROM `用户` "
                + "WHERE `删除状态` <> '已删除' AND ( `角色` IS NULL OR `角色` <> '销售顾问' ) ";

        if (!Strings.isNullOrEmpty(unSaleUserReq.permit)) {
            sql += " and `许可` = :permit";
            params.put("permit", unSaleUserReq.permit);
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.username)) {
            sql += " and `用户名` like :username";
            params.put("username", "%" + unSaleUserReq.username + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.realname)) {
            sql += " and `人员姓名` like :realname";
            params.put("realname", "%" + unSaleUserReq.realname + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.mobile)) {
            sql += " and `人员电话` like :mobile";
            params.put("mobile", "%" + unSaleUserReq.mobile + "%");
        }

        if (!Strings.isNullOrEmpty(unSaleUserReq.role)) {
            sql += " and `角色` like :role";
            params.put("role", "%" + unSaleUserReq.role + "%");
        }
        return this.getCount(sql, params);
    }

    @Override
    public List<String> getSaleGroupName(String permit) {
        Map<String, String> params = Maps.newHashMap();
        String sql = "select 组名 " + "from 销售顾问分组 where 1=1 ";

        if (!Strings.isNullOrEmpty(permit)) {
            sql += " and 许可=:permit";
            params.put("permit", permit);
        }
        return this.getList(sql, params);
    }

    @Override
    public PageData getWorkFlow(SetService.workFlowRequest workFlowReq) {
        Map<String, String> params = Maps.newHashMap();

        String permit = Strings.isNullOrEmpty(workFlowReq.permit) ? ""
                : workFlowReq.permit;
        String username = Strings.isNullOrEmpty(workFlowReq.username) ? ""
                : workFlowReq.username;

        params.put("permit", permit);
        params.put("username", username);

        String sql = "SELECT aa.`品牌`,aa.`来源`,aa.`级别`,aa.`车型`,aa.`车型详细`,aa.`销售顾问`,aa.`订单客户姓名`,aa.`订单客户电话`,"
                + "aa.`折后价`,aa.`颜色`,aa.`内饰`,bb.step,bb.transcation_code,bb.quotedPriceCodeOrVIN "
                + "FROM ("
                + "SELECT t4.`品牌`,t4.`来源`,t4.`级别`,t4.`车型`,t4.`车型详细`,t2.`销售顾问`,"
                + "t1.`订单客户姓名`,t1.`订单客户电话`,t2.`折后价`,t5.`颜色`,t5.`内饰`,"
                + "t1.订单申请日期,t3.人员姓名,t2.报价编号,t2.许可 "
                + "FROM 报价订单表 t1 "
                + "JOIN 报价单表 t2 ON t1.订单编号 = t2.订单编号 "
                + "JOIN 用户 t3 ON t3.用户名 = t2.销售顾问 "
                + "JOIN LSH车型 t4 ON t2.车型编号 = t4.车型编号 "
                + "JOIN `LSH库存表` t5 ON t5.`库存编号` = t2.`库存编号` "
                + "WHERE t2.许可 = :permit"
                + ") aa "
                + "JOIN ("
                + "SELECT DISTINCT quotedPriceCodeOrVIN,transcation_status,transcation_code,a.type,a.step "
                + "FROM pushApprovalWorkFlow a "
                + "JOIN ("
                + "SELECT CASE "
                + "WHEN locate(2, statuses) THEN 'closed' "
                + "WHEN RIGHT (statuses, 1) = '1' THEN 'approved'"
                + "WHEN RIGHT (statuses, 1) = '3' THEN 'approved'"
                + "WHEN RIGHT (statuses, 1) = '4' THEN 'approved'"
                + "ELSE	'processing'"
                + "END AS transcation_status,transcation_code AS transcation_code_b "
                + "FROM ("
                + "SELECT group_concat(approvalStatus) AS statuses,transcation_code "
                + "FROM pushApprovalWorkFlow "
                + "WHERE transcation_code IN ("
                + "SELECT transcation_code "
                + "FROM pushApprovalWorkFlow "
                + "WHERE permit = :permit AND type IN ('1') AND userName = :username"
                + ") GROUP BY transcation_code ORDER BY step "
                + ") temp"
                + ") b ON a.transcation_code = b.transcation_code_b "
                + "ORDER BY a.transcation_code DESC"
                + ") bb ON aa.报价编号 = bb.quotedPriceCodeOrVIN";

        return this.getPage(sql, params, workFlowReq.getPageSize(),
                workFlowReq.getPageIndex());
    }

    @Override
    public PageData getLshCarProInfo(@RequestBody SetService.permitRequest permitReq) {
        Map<String, String> params = Maps.newHashMap();

        String sql = "SELECT b.`来源`, b.`品牌`, b.`级别` 车系, b.`车型`, "
                + "b.`车型详细` 车款, a.`A保成本`, a.`A保MSRP` A保市价, a.`B保成本`, "
                + "a.`B保MSRP` B保市价, a.`逸蓝卡成本1A1B` 逸蓝卡成本（1A1B）, "
                + "a.`逸蓝卡MSRP1A1B` 逸蓝卡市价（1A1B）, a.`逸蓝卡成本2A2B` 逸蓝卡成本（2A2B）, "
                + "a.`逸蓝卡MSRP2A2B` 逸蓝卡市价（2A2B）, a.`逸蓝卡成本3A3B` 逸蓝卡成本（3A3B）, "
                + "a.`逸蓝卡MSRP3A3B` 逸蓝卡市价（3A3B）, a.`尚银卡成本1年` 尚银卡成本（1年）, "
                + "a.`尚银卡MSRP1年` 尚银卡市价（1年）, a.`尚银卡成本2年` 尚银卡成本（2年）, "
                + "a.`尚银卡MSRP2年` 尚银卡市价（2年）, a.`SSSC`, a.`创建时间`, a.`更新时间`, "
                + "a.`项目成本编号`, a.`更新账号`,a.启用时间,a.停用时间  " + "FROM `LSH随车项目设置` AS a "
                + "INNER JOIN `LSH车型` AS b ON a.`车型编号` = b.`车型编号` "
                + "WHERE a.`许可` = :permit ";

        if (!Strings.isNullOrEmpty(permitReq.permit)) {
            sql += " OR a.`许可` = :permit";
            params.put("permit", permitReq.permit);
        }

        sql += " ORDER BY a.`更新时间` DESC, a.`车型编号`, a.`项目成本编号`";
        return getPage(sql, params, permitReq.getPageSize(), permitReq.getPageIndex());
    }

    /**
     * 最后修改时间16/10/31  11.30
     * 出入账列表获取
     */
    @Override
    public PageData getInOutAccount(SetService.inOutAccountRequest accRequset, String permit) {
        Map<String, String> params = Maps.newHashMap();
        String payPurpose = accRequset.payPurpose;
        String headsql = "  select * from ( ";
        String lastsql = "  ) AS C   where  1=1     ";
        String outmidsql = " ";//调出部分
        String inmidsql = " ";//调入部分
        String newmidsql = " ";//新车部分
        // 调出收款
        String outSQL = "select * from( SELECT " +
                "  a.id  id, " +
                "  '调出' 付款目的, " +
                "  c.`车型` 意向车型, " +
                "  c.`车型详细` 意向车款, " +
                "  b.`车架号` `车架号(VIN)`, " +
                "  b.调车物流状态, " +
                "  '  ' 客户名称, " +
                "  '  ' 客户电话, " +
                "  ' ' 销售顾问, " +
                "  ' ' 新车销售状态, " +
                "  ' ' 销售类型, " +
                "  a.out_confirm_date 调出确认日期, " +
                "  a.expectedLateInStockDate  调入期望最晚入库日期, " +
                "  a.expectedLateMoneyInAccount 调入期望最晚款项到账日期," +
                "  IF (a.is_emegency = '0','是','否') 是否紧急调车, " +
                "  a.invoice_issuing_date 调车增票开出日期, " +
                "  b.`协商调车款项金额`, " +
                "  ' ' `实际到账总额`, " +
                "  a.actual_payment_amount 调入调车款项实付金额, " +
                "  ' '  调入调车款项实付日期, " +
                "  round(a.money_transferd_amount, 2) 调出调车款项实收金额, " +
                "  a.money_transferd_date as 调出调车款项实收日期, " +
                "  d.`经销商名` 调出调入经销商名, " +
                "  a.`out_confirm_date` 排序时间, " +
                "  a.out_invoice_remark 备注,  " +
                "  ' '   `实际到账金额＞应收金额备注`," +
                "  b.`调车财务状态` 财务状态, " +
                "  ' '  报价编号 ," +
                "  ' ' 初始报价单号," +
                "  ' '  挂账客户,  " +   // 是否挂账
                "  ' '  customerType " +  // 开票客户身份证类型
                "FROM (select * from car_transfer_log a  " +
                "        join (select max(id) maxId from car_transfer_log group by stock_code) b on a.id = b.maxId) AS a " +
                "LEFT JOIN `lsh库存表` AS b ON a.stock_code = b.`库存编号` " +
                "LEFT JOIN `lsh车型` AS c ON b.`车型编号` = c.`车型编号` " +
                "LEFT JOIN `lsh经销商表` AS d ON b.`调入经销商` = d.`许可_BMBS` " +
                "WHERE `调出经销商` =:permit  " +
                "AND  (调车财务状态 = '调车付款完成' OR 调车财务状态 = '调车款项到账') ";
        //  调入付款
        String inSQL = "SELECT " +
                "  c.id  id, " +
                "  '调入' 付款目的, " +
                "  b.`车型` 意向车型, " +
                "  b.`车型详细` 意向车款, " +
                "  a.`车架号` `车架号(VIN)`, " +
                "  a.`调车物流状态`, " +
                "  c.custom_name 客户名称, " +
                "  c.custom_tel 客户电话, " +
                "  k.`人员姓名` 销售顾问, " +
                "  d.当前状态 新车销售状态, " +
                "  l.`销售类型`, " +
                "  c.out_confirm_date `调出确认日期`, " +
                "  c.expectedLateInStockDate 调入期望最晚入库日期, " +
                "  c.expectedLateMoneyInAccount 调入期望最晚款项到账日期, " +
                "  IF ( " + " c.is_emegency = '0', " + " '是', " + " '否' " + ") 是否紧急调车, " +
                "  c.invoice_issuing_date 调车增票开出日期, " +
                "  a.`协商调车款项金额`, " +
                "  '  ' `实际到账总额`, " +
                "  c.actual_payment_amount 调入调车款项实付金额, " +
                "  c.actual_payment_date 调入调车款项实付日期, " +
                "  a.`调车款项到帐金额` 调出调车款项实收金额, " +
                "  a.`调车款项到帐日期` 调出调车款项实收日期, " +
                "  j.`经销商名` `调出调入经销商名`, " +
                "  c.invoice_issuing_date 排序时间 ," +
                "  ' ' 备注, " +
                "  ' ' `实际到账金额＞应收金额备注`," +
                "  ' ' 财务状态," +
                "  ' ' 报价编号 , " +
                "  ' ' 初始报价单号," +
                "  ' '  挂账客户,  " +   // 是否挂账
                "  ' '  customerType " +  // 开票客户身份证类型
                "FROM `LSH库存表` AS a " +
                "LEFT JOIN `LSH车型` AS b ON a.`车型编号` = b.`车型编号` " +
                "LEFT JOIN (select * from car_transfer_log a    " +
                "  join (select max(id) maxId from car_transfer_log group by stock_code) b on a.id = b.maxId) c ON a.`库存编号` = c.stock_code " +
                "LEFT JOIN `报价单表` AS d ON a.`报价编号` = d.`报价编号` " +
                "LEFT JOIN `LSH经销商表` AS j ON a.`调出经销商` = j.`许可_BMBS` " +
                "LEFT JOIN `用户` AS k ON a.`申请销售顾问` = k.`用户名` " +
                "LEFT JOIN `报价订单表` AS l ON d.`订单编号` = l.`订单编号` " +
                "WHERE a.`调入经销商` = :permit " +
                "AND (a.`调车财务状态` = '调车发票完毕' OR a.`调车财务状态` = '调车付款完成' OR a.`调车财务状态` = '调车款项到账')";
        //新车销售
        String sqlStr新车 = "  SELECT   DISTINCT " +
                "  '  '  id, " +
                "  '新车销售' 付款目的, " +
                "  b.`车型` 意向车型, " +
                "  b.`车型详细` 意向车款, " +
                "  if( locate('IRIS',a.`车架号`)>0,'',a.`车架号`) as`车架号(VIN)`,  " +
                "  '  '  调车物流状态 ," +
                "  l.`订单客户姓名` 客户名称, " +
                "  N .`电话号码` 客户电话, " +
                "  k.`人员姓名` 销售顾问, " +
                "  CASE                      " +
                "     WHEN  locate('IRIS', a.`车架号`) > 0  THEN '8' " +
                "     WHEN NOT ISNULL(m.`交车日期`) THEN '18' " +
                "     WHEN NOT ISNULL(e.`应付金额`) AND NOT ISNULL(e.`实付金额`) AND ROUND(e.`实付金额`,4)>=ROUND(e.`应付金额`,4) THEN '15' " +
                "     WHEN NOT ISNULL(m.`合同签约日期`) THEN '16' " +
                "     ELSE '16'  " +
                "  END AS  新车销售状态   ," +
                "  l.`销售类型` `销售类型`, " +
                "  ' ' 调出确认日期, " +
                "  ' ' 调入期望最晚入库日期, " +
                "  ' ' 调入期望最晚款项到账日期, " +
                "  ' ' 是否紧急调车, " +
                "  ' '调车增票开出日期, " +
                "  ' ' `协商调车款项金额`, " +
                "  e.`实付金额` `实际到账总额`, " +
                "  '  ' 调入调车款项实付金额, " +
                "  ' ' 调入调车款项实付日期, " +
                "  ' '  调出调车款项实收金额, " +
                "  ' '  调出调车款项实收日期, " +
                "  ' ' 调出调入经销商名, " +
                "  l.`订单审批通过日期` 排序时间 , " +
                "  ' ' 备注, " +
                "  e.`财务备注`  `实际到账金额＞应收金额备注`," +
                "  ' ' 财务状态, " +
                "  d.`报价编号`  报价编号, " +
                "  d.初始报价单号,  " +
                "  case d.onCredit when 1 then '是' when 0 then '否' else ' ' end as 挂账客户, " +   // 是否挂账
                "  m.开票客户身份证类型  customerType " +  // 开票客户身份证类型
                "FROM `LSH库存表` AS a " +
                "LEFT JOIN `LSH车型` AS b ON a.`车型编号` = b.`车型编号` " +
                "LEFT JOIN `报价单表` AS d ON a.`报价编号` = d.`报价编号` " +
                "LEFT JOIN `报价付款详细` AS e ON d.`报价编号` = e.`报价编号`   AND e.`付款事由`='总计' " +
                "LEFT JOIN `用户` AS k ON d.`销售顾问` = k.`用户名` " +
                "LEFT JOIN `报价订单表` AS l ON d.`订单编号` = l.`订单编号` " +
                "LEFT JOIN `报价交车表` AS m ON d.`交车` = m.`编号` " +
                "LEFT JOIN `顾客记录汇总` AS n ON d.`顾客编号` = n.`顾客编号` " +
                "JOIN (  " +
                "  SELECT DISTINCT quotedPriceCodeOrVIN  \n" +
                "  FROM pushapprovalworkflow c   \n" +
                "  INNER JOIN ( SELECT id, concat(step, transcation_code) asss   FROM  pushapprovalworkflow   WHERE  PERMIT=:permit ) a   on c.id=a.id \n" +
                "  INNER JOIN ( SELECT concat(max(step), transcation_code) ass  FROM  pushapprovalworkflow    WHERE  PERMIT=:permit GROUP BY  transcation_code  ) b   on b.ass=a.asss  \n" +
                "  where   c.type IN ( 3,10)   AND  PERMIT=:permit AND ( c.approvalstatus = 1 OR c.approvalstatus = 3 OR c.approvalstatus = 9 or c.approvalstatus is NULL )\n" +
                "  AND locate('/', c.transcation_code) = 0 " +
                ") AS k ON k.quotedPriceCodeOrVIN=a.报价编号  " +
                "WHERE a.`所属经销商` =:permit ";
        if (!Strings.isNullOrEmpty(accRequset.carSellType)) {
            outmidsql = " ";
            inmidsql = " AND l.`销售类型` LIKE :carSellType ";
            newmidsql = "  AND l.`销售类型` LIKE :carSellType ";
            lastsql=lastsql+ "   AND c.`销售类型` LIKE :carSellType ";
            params.put("carSellType", accRequset.carSellType);
        }

        if (!Strings.isNullOrEmpty(accRequset.carType)) {
            outmidsql = outmidsql + " AND (" +
                    "c.`品牌` LIKE :carType " +
                    "OR c.`级别` LIKE :carType " +
                    "OR c.`车型` LIKE :carType " +
                    "OR c.`车型详细` LIKE :carType " +
                    ")";
            inmidsql = inmidsql + " AND (" +
                    "b.`品牌` LIKE :carType " +
                    "OR b.`级别` LIKE :carType " +
                    "OR b.`车型` LIKE :carType " +
                    "OR b.`车型详细` LIKE :carType " +
                    ")";
            newmidsql = newmidsql + " AND (" +
                    "b.`品牌` LIKE :carType " +
                    "OR b.`级别` LIKE :carType " +
                    "OR b.`车型` LIKE :carType " +
                    "OR b.`车型详细` LIKE :carType " +
                    ")";

            params.put("carType", "%" + accRequset.carType + "%");
        }

        if (!Strings.isNullOrEmpty(accRequset.carId)) {
            outmidsql = outmidsql + " AND b.`车架号` LIKE :carId ";
            inmidsql = inmidsql + " AND a.`车架号` LIKE :carId ";
            newmidsql = newmidsql + " AND a.`车架号` LIKE :carId ";
            params.put("carId", "%" + accRequset.carId + "%");
        }

        if (!Strings.isNullOrEmpty(accRequset.intelFlowStatus)) {
            outmidsql = outmidsql + " AND b.`调车物流状态` LIKE :intelFlowStatus ";
            inmidsql = inmidsql + " AND a.`调车物流状态` LIKE :intelFlowStatus ";
            newmidsql = newmidsql + " AND a.`调车物流状态` LIKE :intelFlowStatus ";
            lastsql=lastsql+ "  AND c.`调车物流状态` LIKE :intelFlowStatus ";
            params.put("intelFlowStatus", "%" + accRequset.intelFlowStatus);
        }
        outmidsql = outmidsql +"  ) h  where 1=1  ";
        if (!Strings.isNullOrEmpty(accRequset.name) ) {
            outmidsql = outmidsql + " AND h.`客户名称` LIKE :uname  ";
            newmidsql = newmidsql + " AND l.`订单客户姓名` LIKE :uname ";
            inmidsql = inmidsql + " AND c.custom_name LIKE :uname ";
            params.put("uname", "%" + accRequset.name + "%");
        }

        if (!StringUtils.isNullOrBlank(accRequset.onCredit)) {
            lastsql = lastsql + " and c.挂账客户 = :onCredit ";
            params.put("onCredit",accRequset.onCredit);
        }

        lastsql=lastsql+"  ORDER BY 排序时间 DESC";
        String allslq = headsql + (inSQL + inmidsql) + " UNION ALL " + (outSQL + outmidsql) + " UNION ALL " + (sqlStr新车 + newmidsql) + lastsql;
        String sqlStr调入 = headsql + inSQL + inmidsql + lastsql;
        String sqlStr调出 = headsql + outSQL + outmidsql + lastsql;
        String sqlStrnew = headsql + sqlStr新车 + newmidsql + lastsql;
        params.put("permit", permit);

        if ("调入".equals(payPurpose)) {
            return this.getPage(sqlStr调入, params, accRequset.getPageSize(), accRequset.getPageIndex());
        } else if ("调出".equals(payPurpose)) {
            return this.getPage(sqlStr调出, params, accRequset.getPageSize(), accRequset.getPageIndex());
        } else if ("新车销售".equals(payPurpose)) {
            return this.getPage(sqlStrnew, params, accRequset.getPageSize(), accRequset.getPageIndex());
        } else {
            return this.getPage(allslq, params, accRequset.getPageSize(), accRequset.getPageIndex());
        }
    }

    // 分页根据条件查询
    @Override
    public PageData getDealerAttachmentPolicy(Integer pageSize,
                                              Integer pageIndex, String permit, String department, String item,
                                              String 车型, String nC_Type) {
        String sqlStr = "SELECT department 随车部门,	car 适用车型,	apperance_code 颜色,	item 随车项目,	`condition` 条件,   IF( devided_si_msrp='1' AND devided_si_cost='1','是','否') AS isSpiltCar,  " +
                "    IF( devided_si_msrp='1' AND devided_si_cost='1','是','否')  分车款随车MSRP,  IF(devided_si_msrp='1' AND devided_si_cost='1', '',siMsrp)  随车MSRP, IF(devided_si_msrp='1' AND devided_si_cost='1', '是','否') AS 分车款随车成本, "
                + " IF(devided_si_msrp='1' AND devided_si_cost='1', '',siCost) 随车成本, IF(devided_si_msrp='1' AND devided_si_cost='1', '',si_cost_percent) 随车成本占比, NC_Type NC分类, totalCountlimit 总数上限, startDate 生效日期, endDate 终止日期, createDate 创建日期, modifyDate 更新日期, "
                + "if(releaseStatus is NULL,'未发布',releaseStatus) 内部发布状态, remark 备注,if(`status`='1','启用',if(`status`='2','停用',if(`status`='3','删除',''))) 状态, "
                + "if(`status`='1','停用',if(`status`='2','启用','')) 操作, '删除' 删除, item_count, id FROM dealerAttachmentPolicy LEFT JOIN `LSH车型` AS a ON a.`车型编号` = car WHERE ( status = '1' OR status = '2' ) ";
        Map params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(permit)) {
            sqlStr += " AND permit=:permit ";
            params.put("permit", permit);
        }
        if (!Strings.isNullOrEmpty(department)) {
            sqlStr += " AND department like :department ";
            params.put("department", "%" + department + "%");
        }
        if (!Strings.isNullOrEmpty(item)) {
            sqlStr += "  AND item like:item ";
            params.put("item", "%" + item + "%");
        }
        if (!Strings.isNullOrEmpty(车型)) {
            sqlStr += " AND (a.品牌 LIKE:pingpai OR a.`级别` LIKE:jibie OR a.`车型` LIKE:chexing OR a.`车型详细` LIKE:chexingDetail)";
            params.put("pingpai", "%" + 车型 + "%");
            params.put("jibie", "%" + 车型 + "%");
            params.put("chexing", "%" + 车型 + "%");
            params.put("chexingDetail", "%" + 车型 + "%");
        }
        if (!Strings.isNullOrEmpty(nC_Type)) {
            sqlStr += " AND NC_Type like:nC_Type ";
            params.put("nC_Type", "%" + nC_Type + "%");
        }
        sqlStr += " AND item_count <> ''  ORDER BY  modifyDate DESC ";
        return this.getPage(sqlStr, params, pageSize, pageIndex);
    }

    // 查询经销商信息
    @Override
    public List getDistributorInfo(String permitBMBS) {
        Map params = Maps.newHashMap();
        String sql = "SELECT 省份, 城市, 大区, 小区, 经销商名, 经销商名_EN, 许可_BMBS, 许可_LSH, NCCode, DMSCode, SalesCode, DealerCode, IMSCode,"
                + "经销商电话, 经销商类型, 开业日期, 经销商类型 经销商总公司, CTA, 经销商全称 Outlet中文, 经销商地址, 经销商地址_EN FROM  `LSH经销商表`"
                + " where 许可_BMBS =:permitBMBS ";
        params.put("permitBMBS", permitBMBS);
        return this.getList(sql, params);
    }

    // 分页查询 LSHMarketSetting
    @Override
    public PageData getLSHMarketSetting(Integer pageSize, Integer pageIndex,
                                        String channel, String permit, String sourceType) {
        String sqlStr = "SELECT 来源分类 奔驰分类,'其他' 标准来源,来源 自建来源, 创建时间,更新时间,if(启用状态 = '启用','已启用','已停用') 状态,"
                + "if(启用状态 = '启用','停用','启用') 操作,市场编号, 更新账号 from `LSH市场设置` "
                + "WHERE 1=1 ";
        Map params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(channel)) {
            sqlStr += " AND `渠道`=:channel ";
            params.put("channel", channel);
        }
        if (!Strings.isNullOrEmpty(permit)) {
            sqlStr += " AND `许可`=:permit";
            params.put("permit", permit);
        }
        if (!Strings.isNullOrEmpty(sourceType)) {
            sqlStr += " AND 来源分类 =:sourceType";
            params.put("sourceType", sourceType);
        }
        return this.getPage(sqlStr, params, pageSize, pageIndex);
    }

    // 查询用户密码
    @Override
    public List getUserPassword(String userName) {
        Map params = Maps.newHashMap();
        String sql = "select 密码 from 用户 where 用户名=:userName";
        params.put("userName", userName);
        return this.getList(sql, params);
    }

    // 获取si最后修改时间用户
    @Override
    public List getUpdAcountAndTime(String permit,Integer siType) {
        Map params = Maps.newHashMap();
        String sql = "SELECT b.n `行数`, b.m `最近更新日期`, a.last_update_user `最近更新账号` FROM income_adjustment "
                + "a JOIN ( SELECT COUNT(id) n, max(last_update_date) m FROM income_adjustment a "
                + "WHERE a.permit=:permit " ;
        //选取只与自己相关的设定记录
        switch (siType){
            case DefaultValueConstant.REPORT_LIVE_SI_INSURANCE:
                sql += " AND ( a.`insurance_poundage_adjust_real` IS NOT NULL OR a.`insurance_poundage_adjust_real` >0) ";
                break;
            case DefaultValueConstant.REPORT_LIVE_SI_FINANCIAL:
                sql += " AND ( a.`finance_poundage_adjust_real` IS NOT NULL OR a.`finance_poundage_adjust_real` >0) ";
                break;
            case DefaultValueConstant.REPORT_LIVE_SI_DISCOUNT:
                sql += " AND ( a.`finance_soft_loan_adjust_real` IS NOT NULL OR a.`finance_soft_loan_adjust_real` >0) ";
                break;
            case DefaultValueConstant.REPORT_LIVE_SI_MARKET:
                sql += " AND ( a.`after_market_product_cost_adjust_real` IS NOT NULL OR a.`after_market_product_cost_adjust_real` >0) ";
                break;
            case DefaultValueConstant.REPORT_LIVE_SI_FIVE_STAR:
                sql += " AND ( a.`five_stars_extend_cost_adjust_real` IS NOT NULL OR a.`five_stars_extend_cost_adjust_real` >0) ";
                break;
            case DefaultValueConstant.REPORT_LIVE_SI_MARKET_BUY:
                sql += " AND ( a.`after_market_product_cost_buy_adjust_real` IS NOT NULL OR a.`after_market_product_cost_buy_adjust_real` >0) ";
                break;
            case DefaultValueConstant.REPORT_LIVE_SI_MARKET_GIVE:
                sql += " AND ( a.`after_market_product_cost_adjust_real` IS NOT NULL OR a.`after_market_product_cost_adjust_real` >0)  ";
                break;
        }
        sql +=  ") AS b WHERE a.permit =:permit AND a.last_update_date = b.m GROUP BY b.m";
        params.put("permit", permit);
        return this.getList(sql, params);
    }

    @Override
    public List getIncomeAdjustmentInfoByFrameNum(String permit, String frameNum,Integer siType ) {
        Map params = Maps.newHashMap();
        String sql = SqlConstant.GET_SI_NUM_LIST;
        if (!Strings.isNullOrEmpty(frameNum)) {
            String[] frameNumArray = frameNum.split(",");
            int length = frameNumArray.length;
            for (int i = 0; i < length; i++) {
                if (i == 0) {
                    sql += " and (a.`车架号` = '" + frameNumArray[i] + "' ";
                } else {
                    sql += " or a.`车架号` = '" + frameNumArray[i] + "' ";
                }
            }
            sql += ")";
        }
        params.put("permit", permit);
        List list = this.getList(sql, params);
        return list;
    }

    // 根据调价查询收入调整信息
    @Override
    public PageData getIncomeAdjustmentByCondition(String permit,
                                                   String shengchanId, String customerName, String openTicketDate,
                                                   String jibie, String carModel, String carVINCode, String type,
                                                   Integer pageSize, Integer pageIndex, Integer siType) {
        Map params = Maps.newHashMap();
        String sqlStr =SqlConstant.GET_SI_LIST;
        params.put("permit", permit);
        params.put("siType", siType);

        //选取只与自己相关的设定记录
       switch (siType){
           case DefaultValueConstant.REPORT_LIVE_SI_INSURANCE:
               sqlStr += " AND ( a.`insurance_poundage_adjust_real` IS NOT NULL OR a.`insurance_poundage_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_FINANCIAL:
               sqlStr += " AND ( a.`finance_poundage_adjust_real` IS NOT NULL OR a.`finance_poundage_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_DISCOUNT:
               sqlStr += " AND ( a.`finance_soft_loan_adjust_real` IS NOT NULL OR a.`finance_soft_loan_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_MARKET:
               sqlStr += " AND ( a.`after_market_product_cost_adjust_real` IS NOT NULL OR a.`after_market_product_cost_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_FIVE_STAR:
               sqlStr += " AND ( a.`five_stars_extend_cost_adjust_real` IS NOT NULL OR a.`five_stars_extend_cost_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_MARKET_BUY:
               sqlStr += " AND ( a.`after_market_product_cost_buy_adjust_real` IS NOT NULL OR a.`after_market_product_cost_buy_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_MARKET_GIVE:
               sqlStr += " AND ( a.`after_market_product_cost_adjust_real` IS NOT NULL OR a.`after_market_product_cost_adjust_real` >0) ";
               break;
           case DefaultValueConstant.REPORT_LIVE_SI_THREE_SECURE:
               sqlStr += " AND ( a.`third_extend_cost_adjust_real` IS NOT NULL OR a.`third_extend_cost_adjust_real` >0) ";
               break;
       }

        if (!Strings.isNullOrEmpty(shengchanId)) {
            sqlStr += " AND ( b.`生产号` IS NULL OR b.`生产号` LIKE:shengchanId ) ";
            params.put("shengchanId", "%" + shengchanId + "%");
        }
        if (!Strings.isNullOrEmpty(customerName)) {
            sqlStr += " AND ( hh.`开票客户姓名` IS NULL OR hh.`开票客户姓名` LIKE:customerName ) ";
            params.put("customerName", "%" + customerName + "%");
        }
        if (null != openTicketDate) {
            sqlStr += " AND ( hh.`开票时间` IS NULL OR hh.`开票时间` LIKE:openTicketDate ) ";
            params.put("openTicketDate", "%" + openTicketDate + "%");
        }
        if (!Strings.isNullOrEmpty(carVINCode)) {
            sqlStr += " AND ( b.`车架号` IS NULL OR b.`车架号` LIKE:carVINCode ) ";
            params.put("carVINCode", "%" + carVINCode + "%");
        }
        if (!Strings.isNullOrEmpty(jibie)) {
            sqlStr += "  AND bb.`级别` LIKE:jibie ";
            params.put("jibie", "%" + jibie + "%");
        }
        if (!Strings.isNullOrEmpty(carModel)) {
            sqlStr += "  AND  bb.`车型` LIKE:carModel ";
            params.put("carModel", "%" + carModel + "%");
        }
        if (!Strings.isNullOrEmpty(type)) {
            sqlStr += "  AND a.type LIKE:type ";
            params.put("type", "%" + type + "%");
        }
        return this.getPage(sqlStr, params, pageSize, pageIndex);
    }

    @Override
    //更新时间 11/3   添加 `应付金额` IS NOT NULL
    public PageData getNewCarSaleDetailInfo(String quotedPrice) {
        Map params = Maps.newHashMap();
        //  String sql = "SELECT `更新时间` `日期`,`付款事由` `付款项目`,`应付金额` `金额` FROM `报价付款详细` where 报价编号 =:quotedPrice  AND `应付金额` IS NOT NULL";
        params.put("quotedPrice", quotedPrice);
        String sql = "select       " +
                "                                IFNULL(c.保险总价,0)+IFNULL(c.车船税,0)+IFNULL(i.购置税,0)+IFNULL(d.上牌服务费,0.0)+IFNULL(d.其他服务费,0)+IFNULL(g.金融服务费,0)+   " +
                "                                IFNULL(f.延保折后价,0)+IFNULL(e.精品折后价,0)+a.折后价+   " +
                "                                        IFNULL(h.置换手续费,0)+IFNULL(f.厂家延保实收,0)+IFNULL(f.第三方延保实收,0)+IFNULL(i.代金券,0)+   " +
                "                                        IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+   " +
                "                                        IFNULL(i.上牌保证金,0)+IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+   " +
                "                                        IFNULL(i.其他七,0)+IFNULL(i.其他一,0)+IFNULL(i.其他二,0)+IFNULL(i.其他三,0)+IFNULL(i.其他四,0)+IFNULL(i.其他五,0) 01订单总价,      " +
                "                                a.折后价 02车身价格,     " +
                "                                IFNULL(b.订金金额,0)+IFNULL(b.订金加减金额,0) 03订金金额, " +
                "                                IFNULL(i.购置税,0) 04购置税,     " +
                "                                IFNULL(c.保险总价,0) `05保险费(交强险+商业险)`,  " +
                "                                IFNULL(c.交强险,0) 06交强险,      " +
                "                                IFNULL(c.商业险,0) 07商业险,      " +
                "                                IFNULL(c.车船税,0) 08车船税,     " +
                "                                IFNULL(e.精品折后价,0)09精品费,      " +
                "                                IFNULL(f.厂家延保实收,0)10厂家延保,      " +
                "                                IFNULL(f.第三方延保实收,0)11第三方延保,      " +
                "                                        IFNULL(f.延保折后价,0.00)12本店延保,    " +
                "                                IFNULL(d.上牌服务费,0.00)13上牌费,  " +
                "                                round(IFNULL(d.其他服务费,0.00),2)14销售服务费,      " +
                "                                IF(g.金融服务费 is null or g.金融服务费 = '',0.00,round(g.金融服务费,2))15贷款服务费,    " +
                "                                        IFNULL(h.置换手续费,0)16置换手续费,    " +
                "                                IFNULL(i.代金券,0)+IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+IFNULL(i.上牌保证金,0)+IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+IFNULL(i.其他七,0) 17预收款,    " +
                "                                        IFNULL(i.其他一,0)+IFNULL(i.其他二,0)+IFNULL(i.其他三,0)+IFNULL(i.其他四,0)+IFNULL(i.其他五,0) 18其他,    " +
                "                                 IF(h.`置换估价` is null or h.`置换估价` = '',0.00,CONCAT('-', h.`置换估价`) )19置换评估价           " +
                "                                from (select 报价编号,订单编号,费用编号,精品,延保编号,金融,折后价,置换编号,预收编号,保险编号 from 报价单表 where 报价编号 = :quotedPrice) a      " +
                "                                LEFT JOIN 报价订单表 b ON a.订单编号 = b.订单编号      " +
                "                                LEFT JOIN (  " +
                "                                  SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险,  " +
                "                                         SUM( IF ( `险种` = '车船税', `折后价`, 0 )) + SUM( IF ( `险种` = '车船税', `不计免赔`, 0 )) AS 车船税,  " +
                "                                         SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,  " +
                "                                         SUM( IF ( `险种` != '车船税', `折后价`, 0 )) + SUM( IF ( `险种` != '车船税', `不计免赔`, 0 )) AS 保险总价,保险编号 FROM `报价保险详细表` WHERE `保险编号` = (select 保险编号 from 报价单表 where 报价编号 = :quotedPrice)  " +
                "                                     ) c ON a.保险编号 = c.保险编号    " +
                "                                LEFT JOIN (select MAX(case when 费用名称 = '上牌服务费' then 费用折后价 end) 上牌服务费 ,      " +
                "                                         MAX(case when 费用名称 = '其他服务费' then 费用折后价 end) 其他服务费,      " +
                "                                         费用编号      " +
                "                                         from 报价费用详细表 GROUP BY 费用编号      " +
                "                                     ) d ON a.费用编号 = d.费用编号      " +
                "                                LEFT JOIN 报价精品表 e ON  a.精品 = e.编号      " +
                "                                LEFT JOIN 报价五星延保 f ON f.延保编号 = a.延保编号      " +
                "                                LEFT JOIN 报价金融表 g ON g.金融编号 = a.金融    " +
                "                                LEFT JOIN   报价置换表 h ON a.置换编号 = h.置换编号    " +
                "                                LEFT JOIN 报价预收表 i ON a.预收编号 = i.预收编号  ";
        List list = new ArrayList();
        List<Map<String,Object>> re = this.getList(sql, params);
        int length = 0;
        Map<String,Object> maps = new TreeMap(re.get(0));
        if(re!=null&&re.size()>0) {
            for (Map.Entry<String,Object> m : maps.entrySet()) {
                Map map = new HashMap();
                map.put("value",m.getValue());
                map.put("key",m.getKey().substring(2));
                list.add(map);
            }
            length = re.get(0).size();
        }
        return new PageData(list,length);
    }

    // 获取 销售项目设置 中的设置项目和值
    public List getSaleItemsSetting(String permit,
                                    String setItems) {
        Map params = Maps.newHashMap();
        String sql = "SELECT a.`设置项目`, a.`值` FROM `销售项目设置` a WHERE a.`许可` =:permit";
        String[] list = setItems.split(",");
        params.put("permit", permit);
        for (int i = 0; i < list.length; i++) {
            if (i == 0) {
                sql += " AND (";
            }
            sql += "a.`设置项目` =:setItems" + i + " ";
            if (i < list.length - 1) {
                sql += " OR ";
            }
            if (i == list.length - 1) {
                sql += ")";
            }
            params.put("setItems" + i + "", list[i]);
        }
        return this.getList(sql, params);
    }

    @Override
    public List<String> getLshProCode(String siCode, String permit) {
        Map params = Maps.newHashMap();
        String sqlStr = "SELECT `产品编码` "
                + "FROM `LSH金融设置` "
                + "WHERE IFNULL(`是否删除,'') <> '是' ";

        if (!Strings.isNullOrEmpty(siCode)) {
            sqlStr += "  AND `SI编码` = :siCode ";
            params.put("siCode", siCode);
        }

        if (!Strings.isNullOrEmpty(permit)) {
            sqlStr += "  AND `许可` = :permit ";
            params.put("permit", permit);
        }

        return this.getList(sqlStr, params);
    }

    @Override
    public List getCarColorByCarTypeCode(int carTypeCode) {
        Map<String, String> params = Maps.newHashMap();
        Lsh车型Model lshCarMode = lshCarTypeRepository.findOne(carTypeCode);

        StringBuffer queryCondition = new StringBuffer(" IFNULL(车型详细, '') <> '' ");

        String source = lshCarMode.get来源();
        if (!Strings.isNullOrEmpty(source)) {
            queryCondition.append(" AND 来源 = :source");
            params.put("source", source);
        }

        String brand = lshCarMode.get品牌();
        if (!Strings.isNullOrEmpty(brand)) {
            queryCondition.append(" AND 品牌 = :brand");
            params.put("brand", brand);
        }

        String level = lshCarMode.get级别();
        if (!Strings.isNullOrEmpty(level)) {
            queryCondition.append(" AND 级别 = :level");
            params.put("level", level);
        }

        String carType = lshCarMode.get车型();
        if (!Strings.isNullOrEmpty(carType)) {
            queryCondition.append(" AND 车型 = :carType");
            params.put("carType", carType);
        }

        String carTypeDetail = lshCarMode.get车型详细();
        if (!Strings.isNullOrEmpty(carTypeDetail)) {
            queryCondition.append(" AND 车型详细 = :carTypeDetail");
            params.put("carTypeDetail", carTypeDetail);
        }

        String queryStr = queryCondition.toString();

        String sqlStr = "select 颜色说明 "
                + "from ("
                + "select count(*) as countNum ,颜色说明 "
                + "from ("
                + "select Variant "
                + "from LSH车型 "
                + "where " + queryStr + ""
                + ") a "
                + "LEFT JOIN `LSH车型外观` b on b.Variant = a.Variant "
                + "group by 颜色说明"
                + ") a "
                + "RIGHT JOIN( "
                + "select count(*) countNum "
                + "from LSH车型 "
                + "where " + queryStr + ") b "
                + "on a.countNum = b.countNum";

        return getList(sqlStr, params);
    }

    //根据id获取非车辆收入调整信息
    @Override
    public List getNoCarIncomeAdjustmentInfo(String permit, String id) {
        Map params = Maps.newHashMap();
        String sql = "SELECT b.`品牌`,if(b.`来源` = 'PbP', CONCAT('PbP ',b.`级别`), b.`级别`) `级别`, "
                + "b.`车型`, b.`车型详细`, " +
                "a.type, a.car_code, a.adjust_date, " +
                "a.si_adjust_real," +
                " a.finance_soft_loan_adjust _real," +
                " a.finance_poundage_adjust_real,"+
                 " a.insurance_poundage_adjust_real," +
                " a.after_market_product_cost_adjust_real," +
                " a.after_market_product_cost_buy_adjust_real," +
                " a.five_stars_extend_cost_adjust_real"+
                " FROM income_adjustment AS a" +
                " LEFT JOIN `LSH车型` AS b ON a.car_code = b.`车型编号` "
                + "WHERE a.permit =:permit AND a.id =:id";
        params.put("permit", permit);
        params.put("id", id);
        return this.getList(sql, params);
    }

    @Override
    public PageData getJiTuanSourceInfo(Integer pageSize, Integer pageIndex, String channel) {
        String sqlStr = "SELECT 来源分类 奔驰分类, 来源 标准来源, 创建时间,更新时间,"
                + "if(启用状态 = '启用','已启用','已停用') 状态,if(启用状态 = '启用','停用','启用') 操作,市场编号, "
                + "更新账号 from `LSH市场设置` WHERE 1=1 AND 来源分类 <> '其他' ";
        Map params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(channel)) {
            sqlStr += " AND 渠道=:channel ";
            params.put("channel", channel);
        }
        return this.getPage(sqlStr, params, pageSize, pageIndex);
    }

    @Override
    public List getCarLastUpdateTime() {
        String sqlStr = "SELECT MAX(`更新时间`) 更新时间, 更新账号 FROM lsh保险设置";
        return getList(sqlStr);
    }

    /**
     * 开票列表
     * 最后更新时间12/24
     */
    @Override
    public PageData getInvoicesList(SetService.purposeRequest poseRequest, String dealer) {
        Map<String, String> params = Maps.newHashMap();
        String purpose = poseRequest.Purpose;
        params.put("dealer", dealer);
        String headsql = "  select * from (";
        String endsql = ") AS C ORDER BY 排序时间 DESC ";
        String sqlStr调车 = "SELECT " +
                "'调车' 开票目的 ," +
                " c.`车型`, " +
                " c.`车型详细`, " +
                " b.`车架号`, " +
                " d.`经销商名`, " +
                " b.`调车物流状态`, " +
                " a.custom_name 顾客姓名, " +
                " a.custom_tel 电话号码, " +
                " e.`人员姓名`, " +
                " g.`销售类型`," +
                "  ' ' 交车, " +
                " IFNULL (b.`调车财务状态` ,'等待开票') 财务状态  , " +
                " a.out_invoice_remark 备注, " +
                " a.invoice_issuing_date 发票开出日期 ," +
                " b.`生产号` ," +
                " a.`out_confirm_date` 排序时间 ," +
                " ' '`初始报价单号`, " +
                "  ' ' `报价编号` " +
                "FROM " +
                "   (select * " +
                "   from car_transfer_log a   " +
                "   join (select max(id) maxId from car_transfer_log group by stock_code) b on a.id = b.maxId) a " +
                "LEFT JOIN `lsh库存表` AS b ON a.stock_code = b.`库存编号` " +
                "LEFT JOIN `lsh车型` AS c ON b.`车型编号` = c.`车型编号` " +
                "LEFT JOIN `lsh经销商表` AS d ON b.`调入经销商` = d.`许可_BMBS` " +
                "LEFT JOIN `用户` AS e ON b.`申请销售顾问` = e.`用户名` " +
                "LEFT JOIN `报价单表` AS f ON b.`报价编号` = f.`报价编号` " +
                "LEFT JOIN `报价订单表` AS g ON f.`订单编号` = g.`订单编号` " +
                "WHERE a.financial_status in ('等待开调车增值票/付款', '调车发票完毕', '调车付款完成', '调车款项到账') " +
//                " ( " +
//                "  调车物流状态 = '调出申请确认' " +
//                "  OR 调车物流状态 = '调出出库' " +
//                "  OR 调车物流状态 = '调入入库' " +
//                " ) " +
                "AND `调出经销商` =:dealer";
        String sqlStr新车 = "SELECT *FROM ( " +
                "  SELECT " +
                " CASE WHEN c.`退票提交时间` IS NOT NULL  THEN '退票' " +
                " WHEN c.`换票提交时间` IS NOT NULL  THEN '换票' " +
                "  ELSE '新车销售'  " +
                " END  AS 开票目的, " +
                " d.`车型`, " +
                " d.`车型详细`, " +
                " a.`车架号`, " +
                " ' ' 经销商名, " +
                " ' ' 调车物流状态, " +
                " (CASE WHEN c.开票客户姓名  IS NULL  OR  TRIM(c.开票客户姓名) = ''   " +
                "      THEN  e.`顾客姓名`  ELSE c.开票客户姓名 END) AS 顾客姓名, " +
                " e.`电话号码`, " +
                " f.`人员姓名`, " +
                " g.`销售类型`," +
                " b.交车, " +
                "   IF ( " +
                " ISNULL(开票时间), " +
                " '未开票', " +
                " '已开票' " +
                " ) AS 财务状态 ," +
                " c.`开票财务备注` 备注, " +
                " c.`开票时间` 发票开出日期, " +
                " a.`生产号`, " +
                " c.`开票提交时间` 排序时间, " +
                " b.`初始报价单号`," +
                " a.`报价编号`" +
                " FROM   `报价交车表` AS c " +
                " LEFT  JOIN `报价单表`  AS b ON c.编号 = b.交车 " +
                " LEFT  JOIN `lsh车型` AS d ON d.`车型编号` = b.`车型编号` " +
                " LEFT  JOIN `顾客记录汇总` AS e ON e.`顾客编号` = b.`顾客编号` " +
                " LEFT JOIN `用户` AS f ON b.`销售顾问` = f.`用户名` " +
                " LEFT  JOIN `lsh库存表`  AS a ON b.`库存编号` = a.`库存编号` " +
                " LEFT JOIN `报价订单表`  AS g ON  g.`订单编号`=b.`订单编号` " +
                " WHERE " +
                " NOT ISNULL(b.`报价编号`) " +
                " AND c.`开票提交时间` IS NOT NULL " +
                " AND  a.`所属经销商` =:dealer " +
                " and c.`退票提交时间` IS  NULL " + " and  c.`换票提交时间` IS NULL ";

        String sqlStr退票 = "SELECT *FROM ( " +
                "  SELECT " +
                " CASE WHEN c.`退票提交时间` IS NOT NULL  THEN '退票' " +
                " WHEN c.`换票提交时间` IS NOT NULL  THEN '换票' " +
                "  ELSE '新车销售'  " +
                " END  AS 开票目的, " +
                " d.`车型`, " +
                " d.`车型详细`, " +
                " a.`车架号`, " +
                " ' ' 经销商名, " +
                " ' ' 调车物流状态, " +
                "(CASE WHEN c.开票客户姓名  IS NULL  OR  TRIM(c.开票客户姓名) = ''   " +
                "      THEN  e.`顾客姓名`  ELSE c.开票客户姓名 END) AS 顾客姓名, " +
                " e.`电话号码`, " +
                " f.`人员姓名`, " +
                " g.`销售类型`, " +
                " b.交车," +
                "   IF ( " +
                "  g.`订单状态`='退票O', " +
                " '未退票', " +
                " '已退票' " +
                " ) AS 财务状态 ," +
                " c.`退票财务备注` 备注, " +
                " c.`退票时间` 发票开出日期, " +
                " a.`生产号`, " +
                " c.`开票提交时间` 排序时间, " +
                " b.`初始报价单号`," +
                " a.`报价编号`" +
                " FROM   `报价交车表` AS c " +
                " LEFT  JOIN `报价单表`  AS b ON c.编号 = b.交车 " +
                " LEFT  JOIN `lsh车型` AS d ON d.`车型编号` = b.`车型编号` " +
                " LEFT  JOIN `顾客记录汇总` AS e ON e.`顾客编号` = b.`顾客编号` " +
                " LEFT JOIN `用户` AS f ON b.`销售顾问` = f.`用户名` " +
                " LEFT  JOIN `lsh库存表`  AS a ON b.`库存编号` = a.`库存编号` " +
                " LEFT JOIN `报价订单表`  AS g ON  g.`订单编号`=b.`订单编号` " +
                " WHERE " +
                " NOT ISNULL(b.`报价编号`) " +
                " AND c.`开票提交时间` IS NOT NULL " +
                " AND  a.`所属经销商` =:dealer " +
                " and c.`退票提交时间` IS NOT NULL " +
                " and c.`退票时间` IS NOT NULL ";
        String sqlStr换票 = "SELECT *FROM ( " +
                "  SELECT " +
                " CASE WHEN c.`退票提交时间` IS  NOT NULL AND g.`订单状态` LIKE '%退票'  THEN '退票' " +
                " WHEN c.`换票提交时间` IS NOT NULL  THEN '换票' " +
                "  ELSE '新车销售'  " +
                " END  AS 开票目的, " +
                " d.`车型`, " +
                " d.`车型详细`, " +
                " a.`车架号`, " +
                " ' ' 经销商名, " +
                " ' ' 调车物流状态, " +
                " (CASE WHEN c.开票客户姓名  IS NULL  OR  TRIM(c.开票客户姓名) = ''   " +
                "      THEN  e.`顾客姓名`  ELSE c.开票客户姓名 END) AS 顾客姓名, " +
                " e.`电话号码`, " +
                " f.`人员姓名`, " +
                " g.`销售类型`," +
                " b.交车, " +
                "   IF ( " +
                " g.`订单状态`='开票O' AND e.`新车销售状态` in ('16' ), " +
                " '未换票', " +
                " '已换票' " +
                " ) AS 财务状态 ," +
                " c.`换票财务备注` 备注, " +
                " c.`换票时间`  发票开出日期, " +
                " a.`生产号`, " +
                " c.`开票提交时间` 排序时间, " +
                " b.`初始报价单号`," +
                " a.`报价编号`" +
                " FROM   `报价交车表` AS c " +
                " LEFT  JOIN `报价单表`  AS b ON c.编号 = b.交车 " +
                " LEFT  JOIN `lsh车型` AS d ON d.`车型编号` = b.`车型编号` " +
                " LEFT  JOIN `顾客记录汇总` AS e ON e.`顾客编号` = b.`顾客编号` " +
                " LEFT JOIN `用户` AS f ON b.`销售顾问` = f.`用户名` " +
                " LEFT  JOIN `lsh库存表`  AS a ON b.`库存编号` = a.`库存编号` " +
                " LEFT JOIN `报价订单表`  AS g ON  g.`订单编号`=b.`订单编号` " +
                " WHERE " +
                " NOT ISNULL(b.`报价编号`) " +
                " AND c.`开票提交时间` IS NOT NULL " +
                " AND  a.`所属经销商` =:dealer " +
                " and  c.`换票提交时间` IS NOT NULL " +
                " and  c.`换票时间` IS NOT NULL ";
        String midsql = " ";
        String midsql2 = " ";
        if (!Strings.isNullOrEmpty(poseRequest.carType)) {
            midsql = midsql + " AND (" +
                    " c.`品牌` LIKE :carType " +
                    " OR c.`级别` LIKE :carType " +
                    " OR c.`车型` LIKE :carType " +
                    " OR c.`车型详细` LIKE :carType " +
                    " ) ";
            midsql2 = midsql2 + " AND (" +
                    " d.`品牌` LIKE :carType " +
                    " OR d.`级别` LIKE :carType " +
                    " OR d.`车型` LIKE :carType " +
                    " OR d.`车型详细` LIKE :carType " +
                    " ) ";
            params.put("carType", "%" + poseRequest.carType + "%");
        }
        if (!Strings.isNullOrEmpty(poseRequest.VIN)) {
            midsql = midsql + " AND " +
                    " b.`车架号` LIKE :VIN ";
            midsql2 = midsql2 + " AND " +
                    " a.`车架号` LIKE :VIN ";
            params.put("VIN", "%" + poseRequest.VIN + "%");
        }
        if (!Strings.isNullOrEmpty(poseRequest.cusTel)) {
            midsql = midsql + " AND " +
                    " a.custom_tel LIKE :cusTel ";
            midsql2 = midsql2 + " AND " + "e.`电话号码` LIKE :cusTel";
            params.put("cusTel", "%" + poseRequest.cusTel + "%");
        }
        if (!Strings.isNullOrEmpty(poseRequest.saleName)) {
            midsql = midsql + " AND " +
                    " e.`人员姓名` = :saleName ";
            midsql2 = midsql2 + " AND " +
                    " f.`人员姓名` = :saleName ";
            params.put("saleName", poseRequest.saleName);
        }
        if (!Strings.isNullOrEmpty(poseRequest.FinancialStatus)) {
            midsql = midsql + " AND " +
                    " b.`调车财务状态` = :FinancialStatus ";
            midsql2 = midsql2 + " AND " + " a.`调车财务状态` = :FinancialStatus ";
            params.put("FinancialStatus", poseRequest.FinancialStatus);
        }


        //查询开票日期的开始时间
        if(!Strings.isNullOrEmpty(poseRequest.startTicket)){
            midsql = midsql + " AND " +
                    "DATE_FORMAT(a.invoice_issuing_date,'%Y-%m-%d') >= DATE_FORMAT(:startTicket,'%Y-%m-%d') ";
            midsql2 = midsql2 + " AND " + " DATE_FORMAT(c.`开票时间`,'%Y-%m-%d') >= DATE_FORMAT(:startTicket,'%Y-%m-%d') ";
            params.put("startTicket", poseRequest.startTicket);
        }
        //查询开票日期的结束时间
        if(!Strings.isNullOrEmpty(poseRequest.finishTicket)){
            midsql = midsql + " AND " +
                    " DATE_FORMAT(a.invoice_issuing_date,'%Y-%m-%d') <= DATE_FORMAT(:finishTicket,'%Y-%m-%d') ";
            midsql2 = midsql2 + " AND " + " DATE_FORMAT(c.`开票时间`,'%Y-%m-%d') <= DATE_FORMAT(:finishTicket,'%Y-%m-%d')";
            params.put("finishTicket", poseRequest.finishTicket);



        }
        midsql2 = midsql2 + " ORDER BY b.`报价编号` DESC ) AS a GROUP BY 初始报价单号 ";

        String sqlStr全 = headsql + sqlStr新车 + midsql2 + " UNION ALL "
                + sqlStr换票 + midsql2 + " UNION ALL "
                + sqlStr退票 + midsql2 + " UNION ALL "
                + sqlStr调车 + midsql + endsql;
        midsql2 = midsql2 + "ORDER BY " +
                " `报价编号` DESC";

        String sqlre = " CASE WHEN c.`退票提交时间` IS NOT NULL  THEN '退票' " +
                " WHEN c.`换票提交时间` IS NOT NULL  THEN '换票' " +
                "  ELSE '新车销售'  " +
                " END  AS 开票目的, ";

        if ("调车".equals(purpose)) {
            sqlStr调车 = headsql + sqlStr调车 + midsql + endsql;
            PageData list调车 = this.getPage(sqlStr调车, params, poseRequest.getPageSize(), poseRequest.getPageIndex());
            return list调车;
        } else if ("新车销售".equals(purpose)) {
            String a = "'新车销售' 开票目的 ";
            sqlStr新车.replace(sqlre, a);
            sqlStr新车 = headsql + sqlStr新车 + midsql2 + endsql;
            PageData list新车 = this.getPage(sqlStr新车, params, poseRequest.getPageSize(), poseRequest.getPageIndex());
            return list新车;
        } else if ("退票".equals(purpose)) {
            sqlStr退票.replace("'退票' 开票目的 ", sqlre);
            sqlStr退票 = headsql + sqlStr退票 + midsql2 + endsql;
            PageData list新车 = this.getPage(sqlStr退票, params, poseRequest.getPageSize(), poseRequest.getPageIndex());
            return list新车;
        } else if ("换票".equals(purpose)) {
            sqlStr换票.replace("'换票' 开票目的", sqlre);
            sqlStr换票 = headsql + sqlStr换票 + midsql2 + endsql;
            PageData list新车 = this.getPage(sqlStr换票, params, poseRequest.getPageSize(), poseRequest.getPageIndex());
            return list新车;
        } else {
            PageData list全 = this.getPage(sqlStr全, params, poseRequest.getPageSize(), poseRequest.getPageIndex());
            return list全;
        }
    }

    //最后更新时间12/24
    public List getInvoicesdetail(String code) {
        Map<String, String> params = Maps.newHashMap();
        params.put("code", code);
        String strsql = "SELECT     " +
                "  g.`销售类型`,     " +
                "  IF (h.`状态` <> '03' and h.`状态` <> '04' and h.状态 <> '12', '是', '否') `是否金融客户`,     " +
                "  c.`开票客户姓名` 客户姓名,     " +
                "  IF (c.`开票客户身份证类型` <> '个人', c.`开票客户机构代码`, c.`开票客户身份证`) 身份证号码,     " +
                "  ' ' 车辆类型,     " +
                "  ' ' `厂牌型号`,     " +
                "  ' ' 产地,     " +
                "  a.`合格证号`,     " +
                "  a.`关单号`,     " +
                "  a.`商检单号`,     " +
                "  a.`发动机号` `发动机号码`,     " +
                "  a.`车架号` `车架号`,     " +
                "  c.`销售价格`,     " +
                "  c.`不含税价`,    " +
                "  c.`换票提交时间`,     " +
                "  c.`换票时间`,     " +
                "  c.`换票财务备注`,     " +
                "  c.`退票提交时间`,     " +
                "  c.`退票时间`,     " +
                "  c.`退票财务备注`,     " +
                "  c.`开票提交时间`,     " +
                "  c.`开票时间`,     " +
                "  c.`开票财务备注`      " +
                "FROM `报价交车表` AS c     " +
                "LEFT JOIN `报价单表` AS b ON c.编号 = b.交车     " +
                "LEFT JOIN `lsh库存表` AS a ON b.`库存编号` = a.`库存编号`     " +
                "LEFT JOIN `报价订单表` AS g ON g.`订单编号` = b.`订单编号`     " +
                "LEFT JOIN  Lsh金融申请  as h on b.`初始报价单号` =h.`初始报价单号`    " +
                "WHERE NOT ISNULL(b.`报价编号`)     " +
                "AND (c.`开票提交时间` IS NOT NULL OR c.`退票提交时间` IS NOT NULL OR c.`换票提交时间` IS NOT NULL )     " +
                "and c.`编号`= :code    " +
                "GROUP BY b.`初始报价单号`     " +
                "ORDER BY b.`报价编号` DESC     ";
        return this.getList(strsql, params);
    }

    @Override
    public List getCarCodeAndCarColorCode(String carDetail, String carColor) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" 	t1.车型编号 carCode, ");
        sb.append(" 	t2.车型外观编号 apperanceCode ");
        sb.append(" FROM ");
        sb.append(" 	LSH车型 t1, ");
        sb.append(" 	LSH车型外观 t2 ");
        sb.append(" WHERE ");
        sb.append(" 	t1.variant = t2.variant ");
        sb.append(" AND t2.颜色说明 = :carColor ");
        sb.append(" AND t1.车型详细 = :carDetail ");
        sb.append(" ORDER BY ");
        sb.append(" 	t1.车型编号, ");
        sb.append(" 	t2.车型外观编号 ");
        Map params = new HashMap();
        params.put("carColor", carColor);
        params.put("carDetail", carDetail);
        return this.getList(sb.toString(), params);
    }


    @Override
    public PageData getIncomeAdjustmentInfoBySi(String permit,
                                            String shengchanId, String customerName, String openTicketDate,
                                            String jibie, String carModel, String carVINCode, String type,
                                            Integer pageSize, Integer pageIndex) {
        Map params = Maps.newHashMap();
        String sqlStr = SqlConstant.GET_SI_BMBS_LIST;
        params.put("permit", permit);

        if (!Strings.isNullOrEmpty(shengchanId)) {
            sqlStr += " AND ( a.`生产号` IS NULL OR a.`生产号` LIKE:shengchanId ) ";
            params.put("shengchanId", "%" + shengchanId + "%");
        }
        if (!Strings.isNullOrEmpty(customerName)) {
            sqlStr += " AND ( i.`开票客户姓名` IS NULL OR i.`开票客户姓名` LIKE:customerName ) ";
            params.put("customerName", "%" + customerName + "%");
        }
        if (null != openTicketDate) {
            sqlStr += " AND ( i.`开票时间` IS NULL OR i.`开票时间` LIKE:openTicketDate ) ";
            params.put("openTicketDate", "%" + openTicketDate + "%");
        }
        if (!Strings.isNullOrEmpty(carVINCode)) {
            sqlStr += " AND ( a.`车架号` IS NULL OR a.`车架号` LIKE:carVINCode ) ";
            params.put("carVINCode", "%" + carVINCode + "%");
        }
        if (!Strings.isNullOrEmpty(jibie)) {
            sqlStr += "  AND c.`级别` LIKE:jibie ";
            params.put("jibie", "%" + jibie + "%");
        }
        if (!Strings.isNullOrEmpty(carModel)) {
            sqlStr += "  AND  c.`车型` LIKE:carModel ";
            params.put("carModel", "%" + carModel + "%");
        }
        if (!Strings.isNullOrEmpty(type)) {
            sqlStr += "  AND income.type LIKE:type ";
            params.put("type", "%" + type + "%");
        }
        return this.getPage(sqlStr, params, pageSize, pageIndex);
    }

    @Override
    public List getIncomeAdjustmentInfoBySiInit(String permit,String id) {
        Map params = Maps.newHashMap();
        String sqlStr = SqlConstant.GET_SI_BMBS_INIT;
        params.put("permit", permit);
        params.put("id",Integer.parseInt(id) );
        return this.getList(sqlStr, params);
    }

    @Override
    public List getOneUserManyAccountList() {
        String sql = "select  " +
                // 伪列用来排序
                "   if(a.approval_group = '', 1, 0) as od,  " +
                "	a.approval_group as approvalGroup,  " +
                "	a.用户名 as userMail,  " +
                "	a.人员姓名 as userNameCn,  " +
                "	a.角色 as userPosition,  " +
                "	(select 经销商名 from lsh经销商表 where 许可_bmbs = a.许可) as dealerNameCn  " +
                "from 用户 a  " +
                "where a.角色 = '总经理'  " +
                "and a.删除状态 <> '已删除'  " +
                "order by od, a.approval_group, userMail  ";
        return this.getList(sql);
    }

    /**
     * 获取非live的经销商列表
     *
     * @return
     */
    @Override
    public List getAllNotLiveDealers() {
        String sql = " SELECT * from not_live_dealer ";
        return this.getList(sql);
    }

    @Override
    public List<报价精品详细表Model> getThirdBoutiques(int quoteNo) {
        String sql = "select a.* from 报价精品详细表 a  " +
                "right join 报价精品表 b on a.编号 = b.编号 " +
                "right join 报价单表 c on b.编号 = c.精品 " +
                "where c.报价编号 = :quoteNo " +
                "and a.origin_type = '1' ";
        Map params = new HashMap();
        params.put("quoteNo",quoteNo);
        return this.getList(sql,params,报价精品详细表Model.class);
    }
}
